Cobol s9 v to sqlloader

I really need help bad with this. The only post out here regarding this issue was to use a free trail editor back in 2001. I have many files coming in from the mainframe that have some columns packed and others not. I cannot get it to load where the columns are S9V99, etc. It also needs to be loaded positionally which really throws things off.

What should the control file be defined as for the comp-3 and the S9V99 columns and what should the table datatype be. I was using decimal and trying the calculation I read but I was getting invalid nibble at times and others values exceeds precision. I tried using zoned decmial and got similar error. Also I tried using the characterset We8ebcdic500 and have no idea what the "fix 801" means placed at the end of the infile line.

Thank you so much

you need t provide an example

your post makes zero sense as it stands

Cobol? Nice, it's like seen an old friend 25 years later!

First of all be sure your input file is a character file meaning you can read it, if you cannot read it Oracle wouldn't read it either.

A fixed position file would be your best bet so, don't be lazy and work on it.

In regards to negative numbers be sure the sign is in the first position of your column then SQL*Loader DECIMAL EXTERNAL should do the trick.

Thank you for taking the time to reply to me. Regarding the example I was given this as a file layout and am taking only a few of the columns from the layout here:


D-SPH-ADR-REC-TYPE PIC X(01).
D-SPH-ADR-FIRM-NO PIC S9(03).
D-SPH-ADR-ACCT-NO PIC S9(09).
D-SPH-ADR-SEQ-NO PIC S9(03).
D-SPH-ADR-SUB-NO PIC S9(03).
D-SPH-ADR-LINES-1 PIC X(40).
D-SPH-ADR-LINES-2 PIC X(40).
D-SPH-ADR-LINES-3 PIC X(40).
D-SPH-ADR-LINES-4 PIC X(40).
D-SPH-ADR-LINES-5 PIC X(40).
D-SPH-ADR-LINES-6 PIC X(40).
D-SPH-ADR-LINES-7 PIC X(40).
D-SPH-ADR-LINES-8 PIC X(40).
D-SPH-ADR-CITY PIC X(30).
D-SPH-ADR-STATE PIC X(02).
D-SPH-ADR-ZIP PIC S9(05).
D-SPH-ADR-ZIP-PLUS4 PIC S9(04).
D-SPH-ADR-COUNTRY-CODE PIC X(04).
D-SPH-ADR-FOREIGN-ZIP PIC X(10).
D-SPH-ADR-DUP-CONFIRM-SW PIC X(01).
D-SPH-ADR-DUP-STATEMENT-SW PIC X(01).
D-SPH-ADR-ALTER-PROXY-SW PIC X(01).
D-SPH-ADR-CONFIRM-COPIES PIC S9(01).
D-SPH-ADR-STATEMENT-COPIES PIC S9(01).
D-SPH-ADR-DIVERT-CODE PIC X(04).

As a test I kept changing the table column definition and the control file for the two columns adr_zip and adr_zip_plus4 from decimal to decimal external to zoned as well as adding the characterset onto the Load Data statement of WE8EBCDIC500 and playing with the Infile line adding the "Fix 801" thinking this Fix tells it how wide a row of the file would be once it unpacks the packed columns. Below is the control file with the characterset taken out and the Fix taken off.

OPTIONS (SILENT=FEEDBACK,DIRECT=FALSE, ERRORS=50, SKIP=1)
LOAD DATA

INFILE '/data/daily_data/maddfull.dat'

APPEND INTO TABLE pbkgcdb.load_nat_madd

(
RECORD_IND POSITION(1) CHAR,
FIRM_NO POSITION(2:4) CHAR,
ACCOUNT_NO POSITION(5:13) DECIMAL(9),
SPH_ADR_SEQ_NO POSITION(14:16) DECIMAL(3),
SPH_ADR_SUB_NO POSITION(17:19) CHAR,
SPH_ADR_LINES_1 POSITION(20:59) CHAR,
SPH_ADR_LINES_2 POSITION(60:99) CHAR,
SPH_ADR_LINES_3 POSITION(100:139)CHAR,
SPH_ADR_LINES_4 POSITION(140:170)CHAR,
SPH_ADR_LINES_5 POSITION(220:259)CHAR,
SPH_ADR_LINES_6 POSITION(260:299)CHAR,
SPH_ADR_LINES_7 POSITION(300:339)CHAR,
SPH_ADR_LINES_8 POSITION(340:369)CHAR,
SPH_ADR_STATE POSITION(370:371)CHAR,
SPH_ADR_ZIP POSITION(372:377)zoned(6),
SPH_ADR_ZIP_PLUS4 POSITION(378:381)DECIMAL

When the characterset was turned on with the Fix set it put garbage in the table. When the characterset and fix are removed the table contains good data for the data that is not PicS9 and for this case zip code is inaccurate data. Ex of the account_no and zipcode

Account_no(wrong) zip code(wrong)
A 00A 3031303030 10


Lastly when I try to load the packed columns that contain +or- signs and decimals from the other files I get the error :
Record 1: Rejected - Error on table PBKGCDB.LOAD_NAT_BKRMASTR, column MST_BAL_MRGN_BAL.
Invalid packed decimal nibble.

I realize unpacking if indeed sqlloader is doing that when it hits the decimal(5,2) or whatever increases the width of the row so how do you know how much it increases the row and how do you accurately determine the start and end positions in the control file when this is done? I have 18 data files and most of them contain some kind of packed column. And I need to know how to appropriately assign the datatype for these unpacked columns in the table. Should it be number with no precision or number with precision and scale.

Thank you very much. This will be ongoing since we have taken over another bank.