DB2 11 for z/OS outsourcing BLOBS

Daniel Luksetich

DB2 11 for z/OS outsourcing BLOBS
Hello all,
I have a requirement to unload table containing BLOBs to an outsourcer.
The requirement is for us to ftp to their site. They are code page
ISO-8899, and we are 037. I don't believe we can unload delimited into a
spanned record, but in any case we are mixing the rest of the data in
the table with the LOB so there will be translation issues. We could
unload the BLOBs to a PDS, but I think PDS's are limited to 1 volume?
Plus, ftping hundres of thousands of BLOBs seems like a challenge in of
itself. The target database is SQL Server. I think I might try loading
the data locally into SQL Server and seeing if I can find a solution
there.

Any suggestions?

Thanks,
Dan

Joe Geller

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Daniel Luksetich)

Dan,

SQL Server Integration Service (SSIS) can read from Db2 and write to SQL Server.  I've used it to migrate data from Db2 for z/OS, but not with BLOBs.  It was actually faster than unloading the table and ftping it.

The other thing to watch out for though is some different datatypes that are not compatible, so you would have some setup work for some data manipulation.  For example, dates/times are different, and dates cannot go back as far as 01/01/0001 (if you have any)  (at least as of SQLServer 2008- could be different now).

Joe

In Reply to Daniel Luksetich:

Hello all,
I have a requirement to unload table containing BLOBs to an outsourcer.
The requirement is for us to ftp to their site. They are code page
ISO-8899, and we are 037. I don't believe we can unload delimited into a
spanned record, but in any case we are mixing the rest of the data in
the table with the LOB so there will be translation issues. We could
unload the BLOBs to a PDS, but I think PDS's are limited to 1 volume?
Plus, ftping hundres of thousands of BLOBs seems like a challenge in of
itself. The target database is SQL Server. I think I might try loading
the data locally into SQL Server and seeing if I can find a solution
there.

Any suggestions?

Thanks,
Dan

Kai Stroh

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Daniel Luksetich)

Dan,

have you looked into the option of putting your unload files into the z/OS Unix file system using TEMPLATE with PATH?

I haven't actually done much with it but it might help you avoid all the quirks of the native z/OS file system, such as dealing with PDS/Es (which are limited to ~500,000 members anyway), worrying about B/D/E37 abends, etc. I think UNLOAD should be able to inline LOBs in the output data stream and prefix them with a 4 byte length field or so. You could then transfer the files via FTP in binary mode to avoid any code page conversion.

Obviously, on the Windows side there needs to be some kind of program to read the files, interpret them correctly, and do the bulk inserts into MSSQL. Unload will give you a SYSPUNCH that matches the structure of the Unix file, so that could serve as "instructions" for the guys running MSSQL.

Best regards

Kai

Jack Campbell

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Daniel Luksetich)

Dan,

Depending on your DB2 version, you may be able to avoid unloading the LOB column to a PDS - recent releases added the option to UNLOAD to a Variable Blocked Spanned dataset - so the LOB data is in the main SYSREC output.

No doubt the SYSREC will be (very) large, but this might be one possible solution.

Sample UNLOAD syntax (LOB column must be listed last with datatype):

  UNLOAD DATA                                 
    FROM TABLE schema.table_name
--  LIMIT 10                                  
    (COL1
    ,COL2
    ,COL3            
    ,COL4_MY_LOB          CLOB                  
    )                                         
    SHRLEVEL CHANGE ISOLATION CS              
--  SHRLEVEL REFERENCE                        
    SPANNED  YES                              
    MAXERR   1            

HTH

Jack                    

Chad Walmer

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Jack Campbell)

For the translation issue, I believe you will need to unload the "regular" columns as UTF-8 and then you will be able to FTP the data and LOBs as binary to avoid translation and allow the vendor to import the records.

 

I have used the VBS option of a vendor unload utility and works very nicely with LOBs and XML but I'm not sure what FTP will do with that format when it's sent to a non-mainframe system (I assume it will just chunk it altogether and add a CR/LF but you will need to test that.)

 

Chad Walmer

Peter Van Paesschen

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Daniel Luksetich)

We use VBS format (believe available since v10) which is very performant compared to unload to PDS (which is also limited to 1 volume) and VB which is limited to max rec length of 32,760 .

Javier Estrada Benavides

RE: DB2 11 for z/OS outsourcing BLOBS
(in response to Peter Van Paesschen)

Hi, how's it going?

  In here, for that kind of requests, we also unload to unix system services using zFS with extended addressability and multi volume under sms routines, and then transfer the output. At least here the unload to a zFS is much faster than usual PDS.

Hope that helps a bit

Regards,

Javier Estrada Benavides, Mexico

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS