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?

WOW!! No responses! Nobody knows how to use DB2 LOAD? Not even the "certified" ones?

LD Bronstein, It would help if you mention the DB2 version and the operating system you are using as it has an impact on the answer.

If you are loading to a DB2 LUW table, look at the MODIFIED BY parameters. There are BINARYNUMERICS, PACKEDDECIMAL, and ZONEDDECIMAL options.

However, to use any of these 3 options it is required that you file be in Postional ASC format.

DB2 9.5, AIX 5.3 (no, it really does not matter - pertinent LOAD syntax has been in place for years)

Finally ... thanks for your response
Already tried - does not work, but perhaps I am missing something
How about specific syntax
I will start

LOAD FROM file1 OF ASC MODIFIED BY ???? METHOD L(1 2) REPLACE INTO X.Y

What exactly do I use instead of "????" for my specific situation? Thx

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

or this:

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.

Thanks.
In MVS this would be no problem at all: C1 POSITION(1:2) SMALLINT - that's all
It's with all these toy "systems" where trouble starts ...
I got it:

load from file1 of asc modified by binarynumerics reclen=3 method l(1 2) replace into x.y

RECLEN modifier is required to use BINARYNUMERICS
Interesting - reclen=3 must include carriage return - that's why 3 and not 2
What a joke - like I said - toys

Thanks again

LD Bronstein. As a long time mainframe z/OS (MVS) DBA, I couldn't agree more.

I actually was at a place that got DB2 AIX V1. Now that was a real 'toy'. It was so much so, that when V2 came out, it was a free upgrade (and IBM does very little (back then) for free).

To be honest the newest versions are pretty good in a lot of respects. It is mostly when you have to deal closely with the operating system that it gets 'messy'.

Mr. Bronstein, May I ask why you're playing with "toys"?

To stay young!! If you never grow up - you never get old!!

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.

Dear Stealth

There is nothing to try. If I use "binarynumerics" the one in ascii will not work. If I omit "binarynumerics" - the one that is binary will not be loaded correctly. Like I said - toys for children ...

Any other certified DB2 experts venture to solve this?

Mr. Bronstein, Why don't you post the answer to your question?