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 *
************************************************** ****************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).|||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|||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)|||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

//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
/*|||And if you really want to smoke their minds...ask them for a comma delimted file

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|||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