Db2

 View Only

  • 1.  LOAD command fails

    Posted Wed January 31, 2024 11:07 AM

    I was provided 2 files that appears to be an export of a DB2 table.  I do not know the source system type or OS.  I was given a layout file (contents below) and a datafile (12GB) that appears to have been created with the UNLOAD command, based on the filename.  This datafile has a .TXT extension, however it is not human readable.  The "file" command returns type: data

    I am trying to load the data into a table on a DB2 11.5.9 database, but multiple errors occur when I try different syntaxes, currently stuck on 

    [db2inst1@rhel-db2-01 ~]$ db2 "LOAD INDDN SYSREC EBCDIC CCSID(37,65534,65534) FORMAT SPANNED YES INTO TABLE PC8CAMPP.DOC_PAGE_IMAGE (IMAGE_ID POSITION(1:6) DECIMAL, PAGE_IMAGE POSITION(7:*) BLOB) NONRECOVERABLE FROM './CCS_UNLOAD_DOC_PAGE_IMAGE_V.TXT'; "

    SQL0104N  An unexpected token "INDDN" was found following "LOAD".  Expected

    tokens may include:  "QUERY".  SQLSTATE=42601

    I am looking for advise on how I can load this data.

    Layout File Contents:

    LOAD DATA INDDN SYSREC

         EBCDIC CCSID(37,65534,65534)

      FORMAT SPANNED YES

      INTO TABLE

    PC8CAMPP.DOC_PAGE_IMAGE

    (IMAGE_ID POSITION(1:6) DECIMAL

    ,PAGE_IMAGE POSITION(7:*) BLOB

    )

    [db2inst1@rhel-db2-01 ~]$ db2level

    DB21085I  This instance or install (instance name, where applicable:

    "db2inst1") uses "64" bits and DB2 code release "SQL11059" with level

    identifier "060A010F".

    Informational tokens are "DB2 v11.5.9.0", "s2310270807", "DYN2310270807AMD64",

    and Fix Pack "0".

    Product is installed at "/opt/ibm/db2/V11.5".



    ------------------------------
    Ernie Dipko
    ------------------------------


  • 2.  RE: LOAD command fails

    IBM Champion
    Posted Thu February 01, 2024 01:43 AM

    To me it looks like you are trying a binary cross-load from Db2 for z/OS into Db2 for LUW. 

    Chances are pretty low, that you will succeed. First of all, Db2 for z/OS and LUW use different code pages. So usually exports are done into ASCII or UTF-8 and converted back into the codepage in your destination database. Second there could be byte order differences between the platforms.

    Can't you directly connect to the database and do a LOAD FROM CURSOR from a federated database?



    ------------------------------
    Roland Schock
    IBM Champion and IBM Gold Consultant
    ------------------------------

    Original Message


  • 3.  RE: LOAD command fails

    Posted Thu February 01, 2024 03:01 AM

    as we see : db2 command prompt on luw

    the load syntax seems to be different from load on db2 luw : LOAD command  

    in luw we only have load from del (csv file) or ixf  or you have to use import and specifying layout

    even if target is db2:z you need to use luw syntax.



    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message


  • 4.  RE: LOAD command fails

    Posted Fri February 02, 2024 09:30 AM

    That is a Db2 for z/OS unload format.
    The first column is fixed length starting in position 1 and is 6 characters long and is in decimal format.
    The second column is starting in position 7 and goes to the end of the record, it is a BLOB.

    If you converted it from EBCDIC to ASCII as it was transferred with EBCDIC to ASCII conversion, there is some bad news.  A decimal number, is signed and whether it is positive or negative depends on the sign bit.  I.e. 1 in any position except from the rightmost is x'F1', but a 1 in the rightmost character will be x'C1' or x'F1' if positive and x'D1' if negative.  Anything in the BLOB would also be converted and unless it is character data, the result will not make sense, some bit combinations may not exist on the receiving end and some will translate to line feed or carriage return.
    If the file is transferred as binary, you will need to convert it yourself.







    ------------------------------
    Tommy Petersen
    ------------------------------

    Original Message