I've got a bit of a problem when creating load ready files our datawarehouse. The problem is that we are using DB2 on the mainframe for our warehouse and we are creating the load ready files on Unix (Sun). While normally this is not a problem, when we are creating files that contain data that represents a varchar field, the DB2 load utility requires all varchar data to be prefixed with a 2 byte binary field (specifically a "PIC S9(4) COMP" data item in COBOL terms) containing the length of the varchar data.
Is there a way to create this compressed field on our Unix platform, or do we move the file to the mainframe and create the true load ready file there?
Any opinions would be appreciated.
Thanks,
Gordon Hutchinson
Creating Compressed Field in Unix
Moderators: chulett, rschirm, roy
You can certainly compute the field lengths, and place the prefix as required.
But maybe you shouldn't be using varchar() types.
What's the target really look like? If you are loading into a known fixed size target, and you can guarantee that your source data is within that size, then define the fields in your loadfiles as char() sized to match the target.
Carter
But maybe you shouldn't be using varchar() types.
What's the target really look like? If you are loading into a known fixed size target, and you can guarantee that your source data is within that size, then define the fields in your loadfiles as char() sized to match the target.
Carter
Hello Gordon,
The internal representation of VarChar fields is a S9(4) COMP integer before the actual characters. If you are using DataStage to creat a flat file, you could build the length column yourself, let's say your text column is called "In.TextField" and your maximum string length is less than 255 then you could use the derivation "CHAR(0):CHAR(LEN(In.TextField))" to generate a binary equivalent value to prepend to the text.
The internal representation of VarChar fields is a S9(4) COMP integer before the actual characters. If you are using DataStage to creat a flat file, you could build the length column yourself, let's say your text column is called "In.TextField" and your maximum string length is less than 255 then you could use the derivation "CHAR(0):CHAR(LEN(In.TextField))" to generate a binary equivalent value to prepend to the text.
</a>