SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Discover the column types of a DB2 DB without Data Dictionary and Documentation


Discover the column types of a DB2 DB without Data Dictionary and Documentation

Author
Message
Paul Hernández
Paul Hernández
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 661
Hi all,

I just want to discuss a method with you to discover the column data types of a table in a DB2 database. I not own the documentation and only a reduce set of tables are exposed, I mean, I only have read access to 4 tables.
At the moment I select all of the columns using a OLEDB Source (MS DB2 Driver) and then connect this source directly to an OLEDB Destination, SQL Server database, and press the "New" button as the target table, just to let SSIS determine (or guess) the columns data type.

The SSIS is good at guessing but I would like to make my own method, like a basic verification of available types.

For instance, if a column has only numbers is numeric, but if it has numbers, NULLs and empty values ("") could also be numeric. But the empty values make the SSIS to marks it as a VARCHAR column, even if the column does not have alphabetic characters.

Any ideas or comment would be appreciated.

Kind Regards.

Paul Hernández
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2465 Visits: 907
how are the tables exposed. Are they on a linked server.

Can you ask the vendor for table definitions. You will probably find that you are not even looking at the tables but some views generated specifically for exposing data to you.

you could try treating each column as varchar until you know better and use a dataConversion task to interrogate rows and change them to the desired values but this would not provide you with a generic approach.

Good luck if the DB2 vendor won't help you

Obiron
Paul Hernández
Paul Hernández
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1108 Visits: 661
Hi Obiron,

Thanks for your comment!

It is a linked server in my Management Studio.

The customer did exactly what you said. All of the fields are at the end Strings. I discovered it because there are no NULL values in the tables, instead there are empty or blank spaces (""). Then depending on the context I treat a field as a numeric, string or datetime field.

The problem here is the customer hired a company for the cash desk system of its stores, another company for the online shop and then our company to create a CRM system, so I have to wait a lot of time until somebody decide who is responsible to deliver documentation and so on.

In previous experiences I've faced similar situations and at the end I received the documentation, but after one or two weeks.

I think it would be nice to develop a tool or a plugin to aim the auto discover data types and length of a given table. I know that SSIS has something similar to tables and flat/csv files, also Pentaho has something similar to csv files, but I don't know exactly how they work internally.

Kind Regards.

Paul Hernández
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search