Mapping Issue from Mainframe file to Datastage

[shamshad]

Hello,

I am trying to read the content of a mainframe file through a FTP Plugin stage and then dumping the content to a teradata table. Below is the mainframe file layout:
-------------------------------------------------------------------------
01 WE_OUT_STK_REC
05 FD_STKOUT_DIV PIC X (01)
05 FD_STKOUT_REG PIC X (02)
05 FD_STKOUT_OTP PIC X (03)
05 FD_STKOUT_VLN PIC X (04)
05 FD_STKOUT_MYR PIC X (04)
05 FD_STKOUT_PYR PIC X (04)
05 FD_STKOUT_PMN PIC X (02)
05 FD_STKOUT_PDT PIC X (10)

05 FD_STKOUT_AGA PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.
-------------------------------------------------------------------

Question:
What should be the mapping COLUMNNAME, SQLTYPE, EXTENDED and LENGTH for each of the row descrived above?

Any help will be greatly appreciated

[shamshad]

I was able to read the file and dump the data to a text file, but all the numeric columns are blank (last 3 columns). I guess the conversion is not happening properly...

What should be the column type in Datastage FTP Plug in Column Type and lenght for last 3 columns for mainframe USAGE COMP fields?

MAINFRAME LAYOUT
----------------------------------------------------------
05 FD_STKOUT_DIV PIC X (01)
05 FD_STKOUT_REG PIC X (02)
05 FD_STKOUT_OTP PIC X (03)
05 FD_STKOUT_VLN PIC X (04)
05 FD_STKOUT_MYR PIC X (04)
05 FD_STKOUT_PYR PIC X (04)
05 FD_STKOUT_PMN PIC X (02)
05 FD_STKOUT_PDT PIC X (10)

05 FD_STKOUT_AGA PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.
-----------------------------------------------------------

DATASTAGE FTP PLUG IN LAYOUT
------------------------------------------------
FD_STKOUT_DIV VARCHAR (01) UNICODE
FD_STKOUT_REG VARCHAR (02) UNICODE
FD_STKOUT_OTP VARCHAR (03) UNICODE
FD_STKOUT_VLN VARCHAR (04) UNICODE
FD_STKOUT_MYR VARCHAR (04) UNICODE
FD_STKOUT_PYR VARCHAR (04) UNICODE
FD_STKOUT_PMN VARCHAR (02) UNICODE
FD_STKOUT_PDT VARCHAR (10) UNICODE

FD_STKOUT_AGA VARCHAR (04)
FD_STKOUT_AGB VARCHAR (04)
FD_STKOUT_AGB VARCHAR (04)
--------------------------------------------------------------------

[rcanaran]

Create a TEXT file with your COBOL layout, starting with the COBL 01 level :

MAINFRAME LAYOUT
01 FD_STKOUT_REC.
05 FD_STKOUT_REG PIC X (02).
05 FD_STKOUT_OTP PIC X (03).
05 FD_STKOUT_VLN PIC X (04).
05 FD_STKOUT_MYR PIC X (04).
05 FD_STKOUT_PYR PIC X (04).
05 FD_STKOUT_PMN PIC X (02).
05 FD_STKOUT_PDT PIC X (10).
05 FD_STKOUT_AGA PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.
05 FD_STKOUT_AGB PIC S9 (9) USAGE COMP.

Then IMPORT the layout into your table-defs. Load the columns from table defs.

The data in COMP fields is BINARY.

IF FTP is sending in BINARY mode, then you should see the corerct values in the numeric fields. However, you must let DS know that this is now a binary EBCDIC record so that it translates your mainframe text.

If FTP is sending in ASCII mode, then it uses a translation table called a CODEPAGE to translate the file byte-by-byte.

If you do not have NLS installed, there is a default internal codepage for Server and Parallel. If NLS is installed, you can install the correct codepages for Server. For Parallel, the default is ISO8859-1. I have not been able to install otehr codepages for parallel.

[shamshad]

For testing purpose, I am only dumping the data to a text file. For all the PIC S9(9) USAGE COMP columns,

What should be my column type and lenght definition (parallel job) at the source?

If I put varchar(4), the target doesn't show any value?

[rcanaran]

1. By "Dumping to a text file", i am assuming that you are ftp'ing to a text file. If your PIC X(?) data (VARCHAR) is readable, then you probably sent with translation to ASCII. Unless your "mainframe" is operating in ASCII mode. Since the data in the COMP fields is BINARY, but FTP doesn't use a layout to translate and transmit, the BINARY data will get translated as though it is character (eg. on the mainframe a decimal value of 64 will store as HEX 40, the codepage will think thats a blank and translate it to HEX 20 (ASCII blank). When you read the number correctly, the value will show decimal 32. If you FTP it with STDIN and STDOUT MODE BINARY, then its untranslated -- great for the numbers -- but now DataStage has to translate the TEXT portions (CHAR, VARCHAR, etc.)

2. To properly read COBOL pacekd and decimal data, it is best to use the actual mainframe layout. IMPORT the layout text to DataStage. The COMP fields will show as INTEGER in the input definition. Try INTEGER with a length of 1 in this case.

3. For a RDBMS definition, try SMALLINT or BYTEINT for storage (I don't know the target dbms).

[thamark]

For testing purpose, I am only dumping the data to a text file. For all the PIC S9(9) USAGE COMP columns,

What should be my column type and lenght definition (parallel job) at the source?

If I put varchar(4), the target doesn't show any value?

Try to use the "Import-->COBOL File Definations" option to load COBOL COPY BOOK metadata. That automatically defines metadata with required properties, which will be easy and best way to do this.

[Bryceson]

Try to make the native type as DISPLAY_NUMERIC for your last three:

FD_STKOUT_AGA DISPLAY_NUMERIC (04)
FD_STKOUT_AGB DISPLAY_NUMERIC (04)
FD_STKOUT_AGB DISPLAY_NUMERIC (04)

Hopefully this will work for you.

Bryceson

[shamshad]

I was able to IMPORT the COPY BOOK Definition. In the source, what should be the column type for the PIC S9(9) USAGE COMP field?

[bcarlson]

Check out this posting for dealing with COMP fields:

Should PIC 9(n) be mapped as Decimal or Character in DS

Also, here's a simple mapping of COBOL datatypes to DataStage types:

Code: Select all

DataStage - COBOL Datatype  Mapping

PIC 9(02) - uint16 / int16 {text} or string[2, padchar=' '];
PIC S9(02) - int16 {text} or string[2, padchar=' '];
PIC 9(9)V99 - decimal[11,2] {zoned};
PIC S9(9)V99 COMP-3 - decimal[11,2] {packed};
PIC S9(9) COMP - int32.  Note: size of int is dependent on input size.  Determine largest value the input type could hold, then determine where it fits: int8, int16, int32 (or uint it not signed).

Hope this helps.

Brad.

[waltzaremba]

Where (what stage, what options...) do you tell DS to translate the EBCDIC data from the mainframe to ACSII? Is there some parm or system variable that needs to be set?


IF FTP is sending in BINARY mode, then you should see the corerct values in the numeric fields. However, you must let DS know that this is now a binary EBCDIC record so that it translates your mainframe text.