Reading LOBs with RCP

[kevinz]

Hello,

I am using the DB2 Connector Stage as a source with RCP on, in order to avoid having to define the columns definition (select * from my_table).

The goal is to load 300+ tables with a generic job, so RCP comes in handy.

However, the issue is when the table has LOBs columns...
This is from IBM:

Note: Reading data from tables containing LOB columns is only supported where the LOB columns are the last columns listed in the select statement. Similarly, viewing data from tables containing LOB columns is only supported where the LOB columns are the last columns defined in the table.
http://www-01.ibm.com/support/knowledge ... ector.html

So for it to work, it seems we must define the columns in the select statement...
By having to do that, we lose pretty much all the usefulness of RCP (eg. not having to modify the select statement if new columns are added later to the table. + extra dev work etc.)

Is there any way I could go around this problem ?

[ShaneMuir]

Its been a while since I used DB2, but I don't remember having that issue. What error do you actually receive? Is your array size set to 1?

Anyway if that doesn't work, and you want to use RCP then you will need to look into generating a select statement prior to executing your job.

You could use the DB2 systables (syscat.columns?) to generate your statement in another job (ensuring that the LOB column is last) and writing the select statement to a file. You then pass this file name to you DB2 connector stage.

[kevinz]

Shane,

this is the error message:
"GenTargetTable,1: The array size must be set to 1 when reading LOBs (CC_OdbcDBRecordDataSetConsumer::dbsAllocateBindBuffers, file CC_OdbcDBRecordDataSetConsumer.cpp, line 785)"

So we added Array Size = 1 to:
1) the target: ODBC Connector Stage for Sybase ASE
2) and source table (DB2) + enable LOB reference set to "No":



That did not help.

Only having the LOBs columns listed last in the Select statement works........

Rrrrr, not cool ! bye bye awesome select * from table + RCP

[ray.wurlod]

How about Shane's suggestion to generate a SELECT statement based on the system table entries? I've done this a number of times in the past (with Oracle sources, primarily) with great success.

[ShaneMuir]

And what error do you get with those settings in the DB2 connector stage?

Rrrrr, not cool ! bye bye awesome select * from table + RCP

And the generation of the select statement, whilst a little more complex does not remove the awesomeness of an RCP job. Its just replacing how the select statement is generated. RCP can still be enabled.

[kevinz]

Yes indeed, we can still keep RCP on, but instead of having nothing to do for the column definition (just select *) we now have to do some extra work and lose a bit of the simplicity in the process (I am also lazy ).

Shane, I understand your suggestion of generating the Select statement based on the Database system tables, however, how can I be sure to always have the LOBs columns last ?
Imagine later on a new VarChar column is added to the table, the same problem will reappear, won't it ?

I am not sure if we got an error message in the DB2 connector Stage with those settings. Let me get back to you on that...

Thanks for your replies guys, it's much appreciated

[rkashyap]

This can be achieved in a two step process. First step to create the 'Select statement' and second one to execute it.

1: Job to create "Select statement".

• 1.1. Select NAME (column-name) and COLTYPE from SYSIBM.SYSCOLUMNS for the table.
  1.2. Sort NAME(column-names) by COLTYPE ensuring that LOB columns are at bottom.
  1.3. Vertically pivot the NAME(column-name).
  1.4. Generate the "Select statement" by concatenating required SQL clauses to pivoted column-names and add necessary delimiters.
  1.5. Store the "Select statement" in a temporary text file or user status area.

2: Pass the stored "Select statement" to Job 2 and execute it in DB2 Connector Stage (with RCP on).

Parm set value file can be used to store the driver variables (TBCREATOR, TBNAME), dynamically created "Select statement" and also for passing the "Select statement" to Job2.

[ShaneMuir]

If I recall, DB2 has a listagg function? If your version does have this function you could get cute and use something like the following to generate your select statement:

Code: Select all

select concat(concat(concat('select ',column_list),' from '),tbname) from 
(select listagg(name,',') within group (order by case when coltype like '%LOB' then 2 else 1 end , colno) as column_list, tbname
from SYSIBM.SYSCOLUMNS
where tbname = '#TableName#'
group by tbname);

That should give you an output of a select statement based on the columns in the syscolumns table with any LOB style columns at the end of the statement.

If you don't have listagg then it would need to be coded in Datastage as rkashyap suggests.