Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2009
    Posts
    23

    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?

  2. #2
    Join Date
    Aug 2009
    Posts
    23
    WOW!! No responses! Nobody knows how to use DB2 LOAD? Not even the "certified" ones?

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  4. #4
    Join Date
    Aug 2009
    Posts
    23
    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

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Aug 2009
    Posts
    23
    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
    Last edited by LD_Bronstein; 08-05-09 at 13:06.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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'.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by LD_Bronstein
    like I said - toys

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

  9. #9
    Join Date
    Aug 2009
    Posts
    23
    To stay young!! If you never grow up - you never get old!!
    Last edited by LD_Bronstein; 08-05-09 at 21:26.

  10. #10
    Join Date
    Aug 2009
    Posts
    23
    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?

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  12. #12
    Join Date
    Aug 2009
    Posts
    23
    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?

  13. #13
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Mr. Bronstein, Why don't you post the answer to your question?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •