IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

BUILD OUTFIL based on condition otherwise empty output file


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Fri Sep 08, 2017 11:06 pm
Reply with quote

Hi Team,

I am using OUTFIL to build 161 byte record by checking column on position 50 with length 2 bytes- data type- binary along with i am doing the sum on values available at positions specified by 'TOT' statment .All columns stands by 'TOT' are sales amount and customer count .
I am facing the issue since build is happening by default .Can you please advise how to use "IF THEN" here to control the build instead of INCLUDE here. since i need an empty output file when there is a value other
than 11 and 14 in column 50 .


Code:

SORT FIELDS=(45,5,ZD,A)  -   DEPT NUMBER FIELD
OUTFIL REMOVECC,NODETAIL,FNAMES=SORTOUT,
INCLUDE=(50,2,BI,EQ,11,OR,50,2,BI,EQ,14),
BUILD=(01,161),
SECTIONS=(45,5,
TRAILER3=(1:C'TOTAL               ',
21:C'11&14TOTAL                   ',
41,40,
TOT=(81,8,PD,TO=PD,LENGTH=08),
TOT=(89,8,PD,TO=PD,LENGTH=08),
TOT=(97,4,BI,TO=BI,LENGTH=04),
TOT=(101,4,BI,TO=BI,LENGTH=04),
TOT=(105,8,PD,TO=PD,LENGTH=08),
TOT=(113,8,PD,TO=PD,LENGTH=08),
TOT=(121,4,BI,TO=BI,LENGTH=04),
TOT=(125,4,BI,TO=BI,LENGTH=04),
TOT=(129,8,PD,TO=PD,LENGTH=08),
137:25X))


Mean while i am also trying from my end to fix this issue .
Thanks
Balaji K
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sat Sep 09, 2017 10:58 am
Reply with quote

Use SUM FIELDS instead of OUTFIL SECTIONS.

Then you will have OUTREC + OUTFIL to play around with the input record.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Sun Sep 10, 2017 3:56 am
Reply with quote

Hi Magesh,

TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sun Sep 10, 2017 11:36 am
Reply with quote

balaji81_k wrote:
Hi Magesh,

TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..



Can you try this and let us know the results.

UNTESTED
Code:

SORT FIELDS=(45,5,ZD,A)                         

SUM FIELDS=(081,8,PD,                           
            089,8,PD,                           
            097,4,BI,                           
            101,4,BI,                           
            105,8,PD,                           
            113,8,PD,                           
            121,4,BI,                           
            125,4,BI,                           
            129,8,PD)   
                       
OUTREC BUILD=(C'TOTAL               ',         
              C'11&14TOTAL                   ',
              41,40,                           
              81,56,                           
              161:X)                           
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sun Sep 10, 2017 11:40 am
Reply with quote

Also if possible, post ur icetool solution.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Sun Sep 10, 2017 7:39 pm
Reply with quote

magesh23586 wrote:
balaji81_k wrote:
Hi Magesh,

TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..



Can you try this and let us know the results.

UNTESTED
Code:

SORT FIELDS=(45,5,ZD,A)                         

SUM FIELDS=(081,8,PD,                           
            089,8,PD,                           
            097,4,BI,                           
            101,4,BI,                           
            105,8,PD,                           
            113,8,PD,                           
            121,4,BI,                           
            125,4,BI,                           
            129,8,PD)   
                       
OUTREC BUILD=(C'TOTAL               ',         
              C'11&14TOTAL                   ',
              41,40,                           
              81,56,                           
              161:X)                           


Hi Magesh,
Sure. Before that i will see what was the issue i have faced when i use sum, it should work fine whatever may be the value +ve or -ve . I come up with two solutions with /without ICETOOL .ICETOOL used with the same code with little changes .

SOLUTION WITH ICETOOL

Code:


SORT FIELDS=(45,5,ZD,A)
OUTFIL FNAMES=SORTOUT,REMOVECC,NODETAIL,
INCLUDE=(50,2,BI,EQ,X'000B',OR,     -> CHECKS FOR '11'
                50,2,BI,EQ,X'000E'),         -> CHECKS FOR '14'
BUILD=(01,161),
SECTIONS=(45,5,
TRAILER3=(1:C'TOTAL               ',
21:C'11&14TOTAL                   ',
41,40,
TOT=(81,8,PD,TO=PD,LENGTH=08),
TOT=(89,8,PD,TO=PD,LENGTH=08),
TOT=(97,4,BI,TO=BI,LENGTH=04),
TOT=(101,4,BI,TO=BI,LENGTH=04),
TOT=(105,8,PD,TO=PD,LENGTH=08),
TOT=(113,8,PD,TO=PD,LENGTH=08),
TOT=(121,4,BI,TO=BI,LENGTH=04),
TOT=(125,4,BI,TO=BI,LENGTH=04),
TOT=(129,8,PD,TO=PD,LENGTH=08),
137:25X))

CTL1:-
COPY FROM (IN1) TO (TEMP01) USING (CTL1) - CTL1 will have that above sort card
COPY FROM(TEMP01) TO(OUT1) USING(CTL2)
CTL2:
SORT FIELDS=COPY
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
                         50,2,BI,EQ,X'000E')





After that i have one more sort card to replace 50th poistion to 90.To indicate this as totals of '11' & '14'.

SOLUTION WITHOUT ICETOOL

Results achieved with empty file but need to test for the sum if i have negative values in BI Field. This is with one SORT CARD.

Code:


SORT FIELDS=(45,5,ZD,A)  -   DEPT NUMBER FIELD
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
                         50,2,BI,EQ,X'000E')
INREC OVERLAY=(1:C'TOTAL                            ',
                         21:C'11&14TOTAL                   ',
                        50:+90,TO=BI,LENGTH=2)
SUM FIELDS=(081,8,PD,                           
            089,8,PD,                           
            097,4,BI,                           
            101,4,BI,                           
            105,8,PD,                           
            113,8,PD,                           
            121,4,BI,                           
            125,4,BI,                           
            129,8,PD)   
OUTREC IFTHEN=(WHEN=(50,2,BI,EQ,X'005A'),
        BUILD=(1,161))





conditional build (WHEN) clause and INCLUDE COND IN SORT helps me to build empty file .However i need to see on the sum for negative .
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Sun Sep 10, 2017 9:32 pm
Reply with quote

magesh23586 wrote:
balaji81_k wrote:
Hi Magesh,

TOT purpose is mainly used to have sum - customer count which is in BI format , in some cases will receive in -ve . SUM is not properly work on when we try to sum it up . Hence we went with TOT=. Also i overcome the issue when i try to use it in ICETOOL with 3 control cards and with temp file in between . however Still i am expecting /working on optimized code for this ..



Can you try this and let us know the results.

UNTESTED
Code:

SORT FIELDS=(45,5,ZD,A)                         

SUM FIELDS=(081,8,PD,                           
            089,8,PD,                           
            097,4,BI,                           
            101,4,BI,                           
            105,8,PD,                           
            113,8,PD,                           
            121,4,BI,                           
            125,4,BI,                           
            129,8,PD)   
                       
OUTREC BUILD=(C'TOTAL               ',         
              C'11&14TOTAL                   ',
              41,40,                           
              81,56,                           
              161:X)                           



Hi Magesh & Team,

I am going with solution without ICETOOL . But when there is a negative value SUM on BI format is not working. I found out from other related forums - I need to use 'FI' format for BI fields with respect to SUM.Please advise it is recommend to use 'FI' format to have SUM on
BI data type fields which will have values of both -ve and +ve in my user cases.
I have tested and it is working fine .

Code:


SORT FIELDS=(45,5,ZD,A)  -   DEPT NUMBER FIELD
INCLUDE COND=(50,2,BI,EQ,X'000B',OR,
                         50,2,BI,EQ,X'000E')
INREC OVERLAY=(1:C'TOTAL                            ',
                         21:C'11&14TOTAL                   ',
                        50:+90,TO=BI,LENGTH=2)
SUM FIELDS=(081,8,PD,                           
            089,8,PD,                           
            097,4,FI,                           
            101,4,FI,                           
            105,8,PD,                           
            113,8,PD,                           
            121,4,FI,                           
            125,4,FI,                           
            129,8,PD)   
OUTREC IFTHEN=(WHEN=(50,2,BI,EQ,X'005A'),
        BUILD=(1,161))

Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sun Sep 10, 2017 10:06 pm
Reply with quote

balaji81_k wrote:
Please advise it is recommend to use 'FI' format to have SUM on
BI data type fields which will have values of both -ve and +ve in my user cases.


FI 1 to 256 bytes Signed fixed-point
BI 1 bit to 4092 bytes Unsigned binary

Answer is Yes,you can use

balaji81_k wrote:

Code:

OUTREC IFTHEN=(WHEN=(50,2,BI,EQ,X'005A'),
        BUILD=(1,161))



Why do you need this ? Try removing above statement and run it, you should get the same output.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Sun Sep 10, 2017 10:12 pm
Reply with quote

Quote:

conditional build (WHEN) clause and INCLUDE COND IN SORT helps me to build empty file .However i need to see on the sum for negative .


I couldnt understand, why do you need an empty file ?
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Sun Sep 10, 2017 10:42 pm
Reply with quote

magesh23586 wrote:
Quote:

conditional build (WHEN) clause and INCLUDE COND IN SORT helps me to build empty file .However i need to see on the sum for negative .


I couldnt understand, why do you need an empty file ?


Hi Magesh,
Will test without OUTREC , before that i need an empty file since i am writing common JCL for 2 different feeds (sales input file) out of which one sales file will not have certain combo (sales channel) (i,e) 11 & 14 . Also i need to know will it be an issue when i try to insert the sum totals to DB2 table because the SUM is done with 'FI' format and those values will get updated/inserted in db2 table columns declared in INTEGER format . Please do let me know is it advisable/must to convert to 'BI' before inserting in db2 table .
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Sun Sep 10, 2017 10:51 pm
Reply with quote

Hi Magesh,

Yes it is working fine without OUTREC . Please advise me on conversion to 'BI' is required ? before inserting /update DB2 table.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Mon Sep 11, 2017 8:02 pm
Reply with quote

I think it should work correctly.

I would suggest you to perform a test before going into production.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 155

PostPosted: Tue Sep 12, 2017 2:28 am
Reply with quote

magesh23586 wrote:
I think it should work correctly.

I would suggest you to perform a test before going into production.


Tested for both +ve and -ve and verified the same in DB2 table columns .Sum / as well as individual records value is not disturbed.
Back to top
View user's profile Send private message
magesh23586

Active User


Joined: 06 Jul 2009
Posts: 213
Location: Chennai

PostPosted: Tue Sep 12, 2017 2:47 am
Reply with quote

Good to hear this is working

Thanks for the update.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top