Archive

Posts Tagged ‘DB2’

DB2 – User defined function to convert list to table ListToTable

March 13, 2011 Leave a comment

Continuing from my previous post SSIS – Pass a list of values to a data flow source.

Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows.

Now I need to have a function on DB2 to convert the list of the Status IDs into rows.

Actually I really need 2 user defined functions.

First, I need one to create a table to tell me where each comma starts and ends.

image

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable_IDX(‘1,101,203’)) AS A

Here is the result telling me where each comma starts and ends.

clip_image002

Second, I  need a function to return me back the actual values as rows in a table.

image

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable(‘1,101,203’)) AS A

Here is the result telling me what each value is as rows in a table.

clip_image002[4]

Here is an example how you can call the user defined function in DB2.

image

Now if I need to use this user defined function in a data source of a data flow step from my SSIS package, taking a parameter, I would write the query link this:

image

 

(The script)

SSIS – Pass a list of values to a data flow source

March 13, 2011 1 comment

I wrote a blog post SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow a few days ago.

But when do we really need the Foreach Loop Container? I find that I do not use it very often. The reason is that I can only pass one at a time the value of the variable to the data source of the data flow inside the Foreach Loop Container. I don’t have a benchmark number of how much overhead that adds to my run time, but if I have 100 values to pass, I start to feel uneasy.

Scenario

I have a list of status IDs I maintain on the SQL server side. Status IDs is one of the dimensions for my fact table. I want to limit the number of records I bring down from our DB2 data warehouse using the list of the Status IDs.

1) Can I maintain the status ID dimension table on DB2? Yes, I can. But I prefer not to do that because by default all our development is supposed to be on SQL server.

2) Can I use MERGE in the data flow, merging my source from DB2 with this Status ID table on SQL Server? Not really. My query on the DB2 side uses several temp tables due to the complexity the query. Without physically staging those temp data, I cannot really use MERGE.

3) Can I create a package variable to hold a dynamic SQL query with a parameter? No. The Status IDs I need to use is not just one value, but a list of many values.

4) How about hard coding those Status IDs in my DB2 query. No. Thanks. No hard coding in this case.

Here is the solution I came up with, and so far it is working perfectly for me.

Step 1: create a SQL table to hold those Status IDs on the SQL Server side

Step 2: in SSIS package, create an Execute SQL Task to read in the Status ID values, and concatenate all the rows into a comma-delimited string, and store the string as a single value into a user defined variable

Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows. (see my next blog post on how I did that)

Step 4: create a data flow with a parameterized data source, and pass the user defined variable into the data source as the parameter.

More to come on this…

%d bloggers like this: