Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: anyone imported text file generated from DB2?

    Has anyone import into SQL Server a text file generated by DB2?

    the DB2 files has bunch of weird characters in it that look like they are used for formatting...as opposed to actually being data.

    Also, SQL can't find a row terminator.

    I received a cobol declaration copy book for the data, but I have no idea what to do with the special characters.
    Any help would be appreiciated.

    There's a sample of the copybook...

    * COBOL DECLARATION FOR TABLE @TNJ00.PL_JUDGM_ATTORNEY *
    ************************************************** ****************
    01 DCLPL-JUDGM-ATTORNEY.
    10 PJATY-ATTORNEY-KEY.
    15 PJATY-JUDGMENT-ID PIC X(14).
    15 PJATY-GROUPING-CDE PIC S9(4) USAGE COMP.
    15 PJATY-ROLE-CDE PIC X(1).
    88 PJATY-CREDITOR VALUE 'C'.
    88 PJATY-DEBTOR VALUE 'D'.
    88 PJATY-TRUSTEE VALUE 'T'.
    15 PJATY-GROUP-SEQ PIC S9(4) USAGE COMP.
    15 PJATY-ENTRY-SEQ PIC S9(4) USAGE COMP.
    10 PJATY-NAME-PREFIX PIC X(4).
    10 PJATY-LAST-NME PIC X(25).
    10 PJATY-FIRST-NME PIC X(12).
    10 PJATY-MIDDLE-NME PIC X(12).
    10 PJATY-NAME-SUFFIX PIC X(4).
    10 PJATY-CITY-NME PIC X(25).
    10 PJATY-STATE PIC X(2).
    10 PJATY-ZIP-CDE PIC X(9).
    10 PJATY-TELEPHONE PIC X(10).
    10 PJATY-LST-MNT-DTE PIC S9(9) USAGE COMP.
    10 PJATY-REC-LOCK-CDE PIC X(1).
    10 PJATY-ALT-NAME.
    49 PJATY-ALT-NAME-LEN PIC S9(4) USAGE COMP.
    49 PJATY-ALT-NAME-TEXT PIC X(75).
    10 PJATY-ADDR-LINE-1.
    49 PJATY-ADDR-LINE-1-LEN PIC S9(4) USAGE COMP.
    49 PJATY-ADDR-LINE-1-TEXT PIC X(50).
    10 PJATY-ADDR-LINE-2.
    49 PJATY-ADDR-LINE-2-LEN PIC S9(4) USAGE COMP.
    49 PJATY-ADDR-LINE-2-TEXT PIC X(50).
    10 PJATY-ADDR-LINE-3.
    49 PJATY-ADDR-LINE-3-LEN PIC S9(4) USAGE COMP.
    49 PJATY-ADDR-LINE-3-TEXT PIC X(50).
    ************************************************** ****************
    * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 20 *
    ************************************************** ****************

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Tell them to send you another file and unpack the numeric fields, and to make the sign a separate character.

    The "USAGE COMP" (usage computational) packs a digit into 4 bits, so that each byte contains two digits. Then COBOL also uses a half byte for the sign unless they specify sign separate and it's location (trailing or leading).

    PIC 9(04) would mean numeric values occupying 4 bytes.
    PIC 9(04) usage comp would mean numeric values occupying 2 bytes
    PIC S9(04) usage comp means numeric values occupying 3 bytes (2 for the numbers and one for the sign with the sign leading the numeric values.


    The 01 level is a record level descriptor. The 10 is a field. the 15 are the sub-fields that make up the field if the 10 level does not have a type identifier. All PIC X fields are fixed length. The 88 level describes the allowed values in the 15 field just above them. The 49 level is the same as the 15 level ... it describes the data in the sub-fields that make up the 10 field.

    Clear as mud, i'm sure. As for the row terminator, you have to calculate the row length and apply your own. You might be able to import this with DTS, but it would be easier to have them send it to you as all character data, them you import and convert to numeric (all numbers in this example are integers (thank goodness) ... but couls have positive or negative signs).

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very good....I thought I was the last salt on the planet that knew this stuff...

    In either case, not only do you have that, you also have varchar's it looks like...which shouldn't be a problem.

    Also, you don't know if any of the columns are nullable.

    What you really need is the LOAD Card that is generated by DB2 when they unload the data. THAT will tell you what you have, not the COBOL Copybook.

    TYhey should probably unload the data using PARM('SQL') and use SQL DML to creat a nice loadable file
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    164 150 141 156 153 040 171 157 165 040 166 145 162 171 040 155 165 143 150

    in octal

    (http://nickciske.com/tools/octal.php)
    Last edited by tomh53; 06-26-06 at 16:32.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You will get CRLF in the file. I've never needed to generate a row terminator...in any event, the Column names are definetly not the column names in DB2...they are limited to 18 bytes...another reason to get the LOAD CARD

    You want them to do this

    Code:
    //UNLOAD JOB (B,X,XXXXX),'UNLOAD',PRTY=13,GROUP=XXXXXXXX,    
    //  NOTIFY=&SYSUID,MSGCLASS=V,TIME=60                        
    //*+JBS BIND XTDDBB4.ONLINE                                  
    //UNCAT    EXEC PGM=IEXUNCAT,COND=(4,LT)                     
    //SYSIN DD *                                                 
      XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606                  
    /*                                                           
    //UNLOAD  EXEC PGM=IKJEFT01,REGION=6M,COND=(4,LT)            
    //STEPLIB   DD DSN=BXXXB4.DB2.SDSNLOAD,DISP=SHR              
    //SYSTSPRT  DD SYSOUT=*                                      
    //SYSPRINT  DD SYSOUT=*                                      
    //SYSUDUMP  DD DUMMY                                         
    //SYSREC00  DD SPACE=(CYL,(100,25),RLSE),                    
    //             UNIT=DASD,DISP=(,CATLG),LABEL=RETPD=365,      
    //             DSN=XXXXXX.DBB4.SBD000DB.UNLOAD.INDEX.D060606 
    //SYSPUNCH  DD DUMMY                                         
    //*SYSPUNCH  DD DISP=SHR,                                    
    //*      DSN=XXXXXX.DBA.DBB4.SBD000DB.CTLCARD(INDEX)         
    //SYSTSIN   DD *                                             
      DSN SYSTEM(DBB4)                                            
      RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -                      
          LIB('BXXXB4.DB2.RUNLIB.LOAD') PARMS('SQL')              
      END                                                         
    /*                                                            
    //SYSIN DD *
    SELECT	  PJATY_JUDGMENT_ID
    	, CHAR(PJATY_GROUPING_CDE,8)	
    	, PJATY_ROLE_CDE
    	, CHAR(PJATY_GROUP_SEQ,8)
    	, CHAR(PJATY_ENTRY_SEQ,8)
    	, PJATY_NAME_PREFIX
    	, PJATY_LAST_NME
    	, PJATY_FIRST_NME
    	, PJATY_MIDDLE_NME
    	, PJATY_NAME_SUFFIX
    	, PJATY_CITY_NME
    	, PJATY_STATE
    	, PJATY_ZIP_CDE
    	, PJATY_TELEPHONE
    	, CHAR(PJATY_LST_MNT_DTE,18)
    	, PJATY_REC_LOCK_CDE
    	, CHAR(PJATY_ALT_NAME_TEXT,75)	
    	, CHAR(PJATY_ADDR_LINE_1_TEXT,50)	
    	, CHAR(PJATY_ADDR_LINE_2_TEXT,50)
    	, CHAR(PJATY_ADDR_LINE_3_TEXT,50)
    FROM TABLE
    /*
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And if you really want to smoke their minds...ask them for a comma delimted file

    Code:
    SELECT	  ' "'||COALESCE(RTRIM(PJATY_JUDGMENT_ID),'')||'"'
    	||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUPING_CDE,8)),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ROLE_CDE),'')||'"'
    	||',"'||COALESCE(RTRIM(CHAR(PJATY_GROUP_SEQ,8)),'')||'"'
    	||',"'||COALESCE(RTRIM(CHAR(PJATY_ENTRY_SEQ,8)),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_NAME_PREFIX),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_LAST_NME),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_FIRST_NME),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_MIDDLE_NME),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_NAME_SUFFIX),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_CITY_NME),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_STATE),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ZIP_CDE),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_TELEPHONE),'')||'"'
    	||',"'||COALESCE(RTRIM(CHAR(PJATY_LST_MNT_DTE,18)),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_REC_LOCK_CDE),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ALT_NAME_TEXT),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_1_TEXT),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_2_TEXT),'')||'"'
    	||',"'||COALESCE(RTRIM(PJATY_ADDR_LINE_3_TEXT),'')||'"'
    FROM TABLE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And if you already have a file based on that layout...you can't use it...or at least you can't use it with out A LOT of coding on your end
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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