File upload through DS

[msmirnov]

Hello all,

I have a task to convert databases from SQL to DB2. There are over 150 tables we need to upload (will be more in a future). Is it possible to use DS to do the work?

We have created a job with ODBC connection to SQL Server and linked it to UDBLoad plug-in for uploading files to DB2. Works just fine.

The question: is it possible to reuse this job (using parameters) for all tables or should we create separate job for each table? If possible, then how to do it?

Thank you in advance!

Max

[raju_chvr]

On the first look at your problem, if you want to re-use the same job how are you going to manage the Metadata which is different for ur tables. More-over the number of rows are also differ. There will be hell number of variables in your job if you even want to try it.

But lets see what other have to say?

[kcbland]

If you have a 1 for 1 conversion then why not simply write a generic script with a table name as parameter to dump the passed table to a file in ordinal column order. Then, write a generic bulk load script to load a file to a target table. Make sure the script dynamically creates your control file defining the target table metadata.

Now, you simply run a script that executes all of the unloads to file. Then, run a script that loads all of the data.

I've done this before, and 1 days of work to do this type of stuff is pretty easy. Your issues are going to be more along the lines of datatype equivalency, constraints, indexes, tablespaces, and data model changes between systems.

[msmirnov]

Thank you very much for your response!!!

That was my original intent - I was going to use bcp on SQL side and LOAD on db2 side to transfer files. The problem is that DB2 does not support some of the SQL data types (smalldate) and these have to be converted on a fly.

Actually, we migrating the data warehouse from MS SQL (with ETL written in stored procs) into DB2/ Data Stage environment. Since we migrate all ETL functions, we figured DS would be a good candidate to help with initial mass conversion of the data.

Any more ideas on how DS can help us?

[kcbland]

You can use DataStage to bridge the two, but your best bet if you insist is to use DataStage to extract and transform the data into bulk-load ready insert files. Then, use DB2 to bulk load. The performance benefits are tremendous, and this way, you can do custom conversions for the datatypes inside the jobs if necessary.

I hope your number of tables is small, and your volume is low. The fastest solution is always bulk unloaders and bulk loaders.

[roy]

Hi,
If the data behind the different types is compatible simply unload to flat file and load from that file.
If you want the DS way it's simple:

Code: Select all

 SQLSERVER => Transformer => DB2

once you enter genral connection info to the stages you can copy the job and reuse it, the only thing you need to do is put the table definitions for the links in the transformer stage,connect them + make conversions if needed and there you have it.
this is some routine work that you could do fairly easy and in a couple of days, depending on number of developers and how many conversions you need, you'll get all 150 tables.
as Ken said "Bulk is beutifulllll" j/k
IHTH

[kcbland]

as Ken said "Bulk is beutifulllll"

In my opinion, if you ain't bulk loading, you ain't data warehousing.*



* PSA/PSDs, EDWs, and Marts, does not include real-time solutions

[clshore]

You're 6.X Server, right?

PX (in 7.X at least), allows you to resolve metadata at runtime using the column export/column import stages.
You can define osh schema files to describe each specific table or file format, then pass in the schema filename as a parm.

So a job that doesn't do field level transformations could be written once.

I believe this happens only once at job start, so there shouldn't be a performance penalty.

Carter