Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Discover the column types of a DB2 DB without Data Dictionary and Documentation Expand / Collapse
Author
Message
Posted Wednesday, March 6, 2013 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1427379
Posted Thursday, March 7, 2013 10:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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
Post #1428141
Posted Friday, March 8, 2013 1:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1428440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse