Unanswered: How to load binary data into DB2 LUW table?
I have a file, where in positions 1-2 a binary number is stored. Table X.Y is created with one column C1 SMALLINT. I would like to load data from this file into table X.Y, e.g. x'0001' in positions 1-2 will result in C1=1, x'00FA' will result in C1= 250, etc. Any ideas?
Here is the complete sentence from the Command Reference manual regrding requirement of Positional ASC data. It mentions the RECLEN option.
This option is supported only with positional ASC, using fixed length records specified by the reclen option.
reclen=x x is an integer with a maximum value of 32 767. x characters are read for each row, and a new-line character is not used to indicate the end of the row.
You can try this:
LOAD FROM file1 OF ASC MODIFIED BY binarynumerics METHOD L(1 2) REPLACE INTO X.Y
LOAD FROM file1 OF ASC MODIFIED BY binarynumerics reclen=? METHOD L(1 2) REPLACE INTO X.Y
? You have to supply the reclen value. I think it would be 5 since you are loading to a SMALLINT column but you could have smaller data values.
I am assuming you have real binary data. If it Packed or Zoned Decimal, you would need one of the other paramters
PS It really does matter what version and especially what operating system you are using. This is a generic DB2 forum that includes z/OS and AS/400 (i series I believe) in addition to LUW. z/OS Load syntax is VERY different from LUW.
And now plot thickens .... I have two numeric fields in as input to the same row in a DB2 LUW table - one binary, the other ascii/ebcdic. Since "modified by binarynumerics" applies to the entire input record, it would seem that I cannot have both formats in the same record, so these data cannot be loaded together - correct? Anyone?
LD_Bronstein, I don't think that is correct. You should be able to load 'mixed' data. Just the correct beginning and ending positions for each 'column' of data along with the correct record length and the data should (theoretically) load.
Since I haven't done this myself, the only way to know for sure is to try it.