Unload from z/OS DSNTIAUL to LUW INGEST on AIX

Tom Glaser

Unload from z/OS DSNTIAUL to LUW INGEST on AIX

Hi,

I’m looking for some help on moving data from DB2 z/OS to DB2 on AIX. I must be doing something wrong as I believe this should work.  DSNTIAUL is unloading data into a file:

000023 SPL   <  2004

       EDD4440401FFFF

       2730000C0C2004

---------------------

000024 SPL   *  2004

       EDD4440501FFFF

       2730000C0C2004

 

I’m now using the INGEST utility to read this file using the following control cards:

 

ingest from file input.DAT format positional                           

       (

        $JOB_GROUP POSITION(1:6) CHAR(6),

        $JOB_GRP_NUM position(7:8) DECIMAL(2,0) external,

        $PROC_PRIORITY_NUM position(9:10) DECIMAL (3,0) external        ,

        $FRMT_INDCTR_CD position(11) CHAR(1),

        $FILE_TYPE_CD position(12) CHAR(3)

        )

        insert into DB2INST1.NEW_TIPABPT                       

                values ( $JOB_GROUP, $JOB_GRP_NUM, $PROC_PRIORITY_NUM, $FRMT_INDCTR_CD, $FILE_TYPE_CD);

 

When I run this utility, I get the following error:

 

SQL2922I The following warning or error occurred while formatting data from

line "37" of input file "TEST.IP.E049403.T41BPTUL.EOL.DAT".

SQL2953N The field value in line "37" starting at byte position "7" cannot be

converted to the value type: "DECIMAL".

 

The INGEST utility says is can handle delimited and positional data. I’ve set this up for positional.  However, when it gets to the 7th byte of the mainframe file, it cannot interpret this data.  I originally had the file sent from z/OS to the AIX server using binary, but found out this had to be text format. 

Any idea what I might be doing wrong?

Thanks, Tom

James Campbell

Unload from z/OS DSNTIAUL to LUW INGEST on AIX
(in response to Tom Glaser)
Don't really know the INGEST utility, but to me it appears that the 7th and 8th bytes are
x'004c' and x'005c'. Which are in an internal decimal format, not "external".

PACKED might be better.

Ditto for $PROC_PRIORITY_NUM position(9:10) DECIMAL (3,0) external

Be very careful about where records end. z/OS data has the philosophy of "I know how long
a record is before I start reading it" (ie either fixed length or with a record length field). *nix
and Windows has the philosophy of "something will tell me when I get to the end of a record"
(usually some combination of CR and/or LF). Great fun can be had when sending a binary
field that contains the magic record end marker from z/OS to *nix/Win. Or indeed a EOF
marker.

Which is really why the UNLOAD utility can generate ASCII or EBCDIC character string
output. (Ditto the ISV equivalents.)

James Campbell

On 1 May 2018 at 13:18, Tom Glaser wrote:

>
> Hi,
> I´m looking for some help on moving data from DB2 z/OS to DB2 on AIX. I must be doing
> something wrong as I believe this should work.  DSNTIAUL is unloading data into a file:
> 000023 SPL   <  2004
>        EDD4440401FFFF
>        2730000C0C2004
> ---------------------
> 000024 SPL   *  2004
>        EDD4440501FFFF
>        2730000C0C2004
>  
> I´m now using the INGEST utility to read this file using the following control cards:
>  
> ingest from file input.DAT format positional                           
>        (
>         $JOB_GROUP POSITION(1:6) CHAR(6),
>         $JOB_GRP_NUM position(7:8) DECIMAL(2,0) external,
>         $PROC_PRIORITY_NUM position(9:10) DECIMAL (3,0) external        ,
>         $FRMT_INDCTR_CD position(11) CHAR(1),
>         $FILE_TYPE_CD position(12) CHAR(3)
>         )
>         insert into DB2INST1.NEW_TIPABPT                       
>                 values ( $JOB_GROUP, $JOB_GRP_NUM, $PROC_PRIORITY_NUM, $FRMT_INDCTR_CD,
> $FILE_TYPE_CD);
>  
> When I run this utility, I get the following error:
>  
> SQL2922I The following warning or error occurred while formatting data from
> line "37" of input file "TEST.IP.E049403.T41BPTUL.EOL.DAT".
> SQL2953N The field value in line "37" starting at byte position "7" cannot be
> converted to the value type: "DECIMAL".
>  
> The INGEST utility says is can handle delimited and positional data. I´ve set this up for positional. 
> However, when it gets to the 7th byte of the mainframe file, it cannot interpret this data.  I originally
> had the file sent from z/OS to the AIX server using binary, but found out this had to be text format. 
> Any idea what I might be doing wrong?
> Thanks, Tom
>


---
This email has been checked for viruses by AVG.
http://www.avg.com

Colin Clayton

Unload from z/OS DSNTIAUL to LUW INGEST on AIX
(in response to Tom Glaser)
Hi Tom,

I suspect the issue is with ‘DECIMAL(p,s) EXTERNAL’


12345678901234

000023 SPL < 2004

EDD4440401FFFF

2730000C0C2004


Your data in bytes 7:8 and 9:10 is packed decimal, x’004C’ and x’001C’

Maybe you need to unload it as EXTERNAL in your DSNTIAUL job ?

Colin



<mailto:[login to unmask email]>[CA] http://www.ca.com/us/default.aspx>[Twitter]<http://twitter.com/CAInc>[Slideshare]<http://www.slideshare.net/cainc>[Facebook] https://www.facebook.com/CATechnologies>[YouTube]<http://www.youtube.com/user/catechnologies>[LinkedIn]<http://www.linkedin.com/company/1372?goback=.cps_1244823420724_1>[Google+]<https://plus.google.com/+CATechnologies [Google+]<http://www.ca.com/us/rss.aspx?intcmp=footernav
CA DB2 Tools Global User Community http://bit.ly/DB2tools
[Cert-Info-Mgmt_color]
<mailto:[login to unmask email]>

From: Tom Glaser [mailto:[login to unmask email]
Sent: 01 May 2018 21:18
To: [login to unmask email]
Subject: [DB2-L] - Unload from z/OS DSNTIAUL to LUW INGEST on AIX


Hi,

I’m looking for some help on moving data from DB2 z/OS to DB2 on AIX. I must be doing something wrong as I believe this should work. DSNTIAUL is unloading data into a file:

000023 SPL < 2004

EDD4440401FFFF

2730000C0C2004

---------------------

000024 SPL * 2004

EDD4440501FFFF

2730000C0C2004



I’m now using the INGEST utility to read this file using the following control cards:



ingest from file input.DAT format positional

(

$JOB_GROUP POSITION(1:6) CHAR(6),

$JOB_GRP_NUM position(7:8) DECIMAL(2,0) external,

$PROC_PRIORITY_NUM position(9:10) DECIMAL (3,0) external ,

$FRMT_INDCTR_CD position(11) CHAR(1),

$FILE_TYPE_CD position(12) CHAR(3)

)

insert into DB2INST1.NEW_TIPABPT

values ( $JOB_GROUP, $JOB_GRP_NUM, $PROC_PRIORITY_NUM, $FRMT_INDCTR_CD, $FILE_TYPE_CD);



When I run this utility, I get the following error:



SQL2922I The following warning or error occurred while formatting data from

line "37" of input file "TEST.IP.E049403.T41BPTUL.EOL.DAT".

SQL2953N The field value in line "37" starting at byte position "7" cannot be

converted to the value type: "DECIMAL".



The INGEST utility says is can handle delimited and positional data. I’ve set this up for positional. However, when it gets to the 7th byte of the mainframe file, it cannot interpret this data. I originally had the file sent from z/OS to the AIX server using binary, but found out this had to be text format.

Any idea what I might be doing wrong?

Thanks, Tom

-----End Original Message-----
Attachments

  • image003.jpg (2.3k)