DSNTIAUL and DB2 Scalar Functions

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Hi Team,

I am executing an SQL query using PGM=IKJEFT01 and PROGRAM(DSNTIAUL). Also, using scalar function CHAR, for extracting the data via an SQL query from single or multiple tables. For this, I have kept the syspunch as dummy. The output file (SYSREC) is generated as a FB file. My first query is can we create a VB file in such a situation? My second issue is that I am getting trailing spaces at the end of each record of the FB output file.

if the same file is a VB file, when we view it in browse mode with HEX ON, it has no spaces but when FB file is viewed in the browse mode with HEX ON, it shows the spaces present.

Below is the job that I am executing.

//UNLOAD EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
LIB('DB2P.LOADLIB') PARM('SQL')
END
/*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DSN=DEV@@ID.SFOCUS.CONV.EPSOMTAF.SUP460,
// DISP=(NEW,CATLG,DELETE),UNIT=(SYSDP,3),
// SPACE=(CYL,(450,550),RLSE)
//SYSIN DD *
SELECT CHAR(COUNT(*) ||',"' ||
SM_MERCH_CAT ||'","' ||
SM_MERCH_CODE ||'",' ||
TO_CHAR(SM_TAF_START_DATE,'YYYYMMDD') ||',' ||
TO_CHAR(SM_TAF_END_DATE,'YYYYMMDD') ||',' ||
SM_TRADING_ADJ_FT ||',"' ||
UPDATE_LOGON_ID ||'","' ||
CHAIN_LETTER ||'"')
FROM OPCSUB.SMVTAF
WHERE NOT STORE_CODE = '4910'
AND NOT CHAIN_LETTER = 'V'
GROUP BY SM_MERCH_CAT, SM_MERCH_CODE, SM_TAF_START_DATE,
SM_TAF_END_DATE, SM_TRADING_ADJ_FT, UPDATE_LOGON_ID,
CHAIN_LETTER
FOR FETCH ONLY;
/*

Output file with HEX ON:

------------------------------------------------------------------------------
1,"IL","CITAIDS.BENEFIT ",20211117,20211130,2.00,"CITAIDS","B"
F67CD767CCECCCE4CCDCCCE476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C7444444444444444444
1BF93FBF3931942B25556930FB20211117B20211130B2B00BF3931942FBF2F000000000000000000
------------------------------------------------------------------------------
1,"IL","CITAIDS.ELOFFER ",20211201,20211207,1.10,"CITAIDS","B"
F67CD767CCECCCE4CDDCCCD476FFFFFFFF6FFFFFFFF6F4FF67CCECCCE767C7444444444444444444
1BF93FBF3931942B53666590FB20211201B20211207B1B10BF3931942FBF2F000000000000000000
------------------------------------------------------------------------------

sergeyken · Posted: Fri Nov 26, 2021 9:18 pm

1) keeping SYSPUNCH as DUMMY, or not - has no effect on your issue.

2) I am on vacation now, so right now I cannot give you a working example: how I successfully created an unloaded dataset with RECFM=VB. Can do it after 12/07/2021

3) Please, use the CODE TAGS when presenting any part of your code

4) the HPU unload utility (if available) can do all your conversions automatically, by default.

5) one different approach would be: to unload the DB2 table without any conversion, and in the next step to process the simple unloaded data using a SORT utility.

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Thank you so much for your reply.

All the columns that are being fetched are ok, just the trailing spaces at the end of each record is an issue. I have tried STRIP, TRIM, REPLACE, etc functions but no success. May be I am not using those functions properly.

Also, I read online that we cannot create a VB file using DSNTIUAL utility. Is that true? I believe those trailing spaces are due to CHAR function or the data conversion thats happening when fetching the records.

What I need is to do something within the SQL query so that trailing spaces do not appear. Please help. Many thanks in advance.

sergeyken · Posted: Sat Nov 27, 2021 1:05 am

All records created by your SQL query are of the same size.
1) calculate the exact number of bytes created by the query (r_size)
2) try to use //SYSREC00 DD …RECFM=FB,LRECL=r_size,…
3) try to use //SYSREC00 DD …RECFM=VB,LRECL=(r_size+4),…

#2 should work.

#3 may not work.
If so, you need to use intermediate temp DD:

Rohit Umarjikar · Posted: Tue Nov 30, 2021 6:11 pm

I suggest to write a Program and done with it when it comes to using functions in SELECT. Easy to write and maintain for future changes.

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Thank you Sergey. I did use the temp dataset and then used the SORT to convert the temp FB file into VB and using VLTRIM to remove the trailing spaces. Like below: ( please note it worked for a few codes successfully but i wonder for below code, the SORT step is not removing trailing spaces from the file, please help)

000011 //UNLOAD EXEC PGM=IKJEFT01
000012 //SYSTSPRT DD SYSOUT=*
000013 //SYSPRINT DD SYSOUT=*
000014 //SYSUDUMP DD SYSOUT=*
000015 //SYSTSIN DD *
000016 DSN SYSTEM(DB2P)
000017 RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) -
000018 LIB('DB2P.LOADLIB') PARM('SQL')
000019 END
000020 /*
000021 //SYSPUNCH DD DUMMY
000022 //SYSREC00 DD DSN=&&TEMPDATA,
000023 // DISP=(,CATLG,DELETE),
000024 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000025 //SYSIN DD *
000026 SELECT CHAR(STORE_NUMBER ||','||
000027 ITEM_CODE ||',"' ||
000028 TO_CHAR((DMD_FC_START_DATE + (7 - DAYOFWEEK(DMD_FC_START_DATE)) DAYS),
000029 'YYYY-MM-DD')||'",'||
000030 AH_WK_DMD_FC ||',"'||
000031 TO_CHAR(DATE_INSERTED,'YYYY-MM-DD')||'","'||
000032 VALUE(TO_CHAR(UPDATE_TIMESTAMP,'YYYY-MM-DD HH:MM:SS'),
000033 '1900-01-01 00:00:00') ||',"'||
000034 VALUE(PURGE_FLAG,' ') ||'","'||
000035 VALUE(REVIEW_FLAG,' ') ||'","'||
000036 TO_CHAR(CURRENT DATE,'YYYY-MM-DD') ||' '||
000037 CHAR(CURRENT TIME,JIS)||'"')
000038 FROM OPCSUB.MAVAHFCT
000039 WHERE DMD_FC_START_DATE >= (CURRENT DATE - 20 DAYS)
000040 FETCH FIRST 100 ROWS ONLY;
000041 /*
000042 //SORTFL EXEC PGM=SORT
000043 //SORTIN DD DSN=&&TEMPDATA,DISP=OLD
000044 //SORTOUT DD DSN=DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246,
000045 // DISP=(NEW,CATLG,DELETE),
000046 // SPACE=(CYL,(5,5),RLSE),UNIT=(3390,30)
000047 //SYSOUT DD SYSOUT=*
000048 //SYSIN DD *
000049 OPTION COPY
000050 OUTFIL FNAMES=SORTOUT,VLTRIM=C' '
000051 /*

The output file - DEV@@ID.SFOCUS.AZUAHFCT.CONV.SUP246 is a VB file (which is good) but trailing spaces are not being removed even when I am using VLTRIM which i have used for other codes as well. Please help. Thanks.

sergeyken · Posted: Thu Dec 16, 2021 5:26 pm

There is not a minor sign that you planned to produce RECFM=VB result…

OUTFIL FTOV,…

Maybe, you’ll need to add extra:
//SORTOUT DD …,LRECL=max_expected_record,…

sergeyken · Posted: Thu Dec 16, 2021 5:49 pm

Please, format your samples like this:

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Thank you so much Sergey for prompt reply.

The thing is for all the codes, I have put that SORT step, which takes the FB temp file and using the FTOV and VLTRIM, its producing the VB file without trailing spaces perfectly. ( I did not had to use the LRECL).

Also, I cannot use the LRECL as records inside the final output file ( file created in the SORT step) are variable in length. Say if 1 record is of 80 LRCEL, another record might be 82, and so on. So do u think SORT would work ok?

Also, one more observation, I tried to run my query by removing this field and then its producing a perfect VB file without trailing spaces. Can you please see what could be the reason. Please help. Thanks.

I removed this field from the query

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Please do not be confused.

The field that I am talking about is

sergeyken · Posted: Fri Dec 17, 2021 1:27 am

Use

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

sergeyken · Posted: Wed Dec 22, 2021 1:33 am

Everything is working when coded properly:

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Thank you so much for prompt reply.

I have a few queries on your suggestion. (I haven't tried it yet)

1. The sort code that I have shared (which I am using for all the jobs) is working OK except a few. Which is surprising. One thing I noticed, which I want to share with you, if the tempdata file has LRCEL of 255 or less, then my SORT code works ok and converts the file into VB and removes the right trailing spaces. But when tempdata file has LRCEL of 256, then it only converts it into VB but does not removes the trailing spaces. Any thoughts on that please? what is going wrong in that case?

2. Do I need to use SYNCSORT for my SORT code? The way you have suggested, using SORTOUT and SORTOUTF?

Many many thanks for your support and help. Really appreciated. Regards. Taran

sergeyken · Posted: Wed Jan 05, 2022 7:03 pm

Rohit Umarjikar · Posted: Wed Jan 05, 2022 11:48 pm

I am surprised to see the issue still not resolved after so many weeks when one program of (1-2 hour max ) could ease the ask.. Good Luck !

Taranprietsingh · New User Joined: 25 Nov 2021 Posts: 10 Location: UK

Hello,

I am using browse mode to open the file and check for the trailing spaces.
Also, I used the SORT code provided by you

sergeyken · Posted: Tue Jan 25, 2022 10:51 pm

Are you sure there is nothing at the end of your input records?

There are no such things as miracles. I have demonstrated you that VLTRIM parameter is working as expected. Analyze ALL your data, ALL your logs, ALL your messages, ALL your datasets (both input, and output!), and ALL your code more carefully. We cannot guess what else is involved into your issue; you even hide the full record content from the forum!

As the first step: copy and paste my working example to be sure it is really working in your environment (I am sure it is). Next, try step by step adding your own data - first as input stream, then - as a separate dataset, and VERIFY THE CONTENT OF YOUR INPUT when running!

Before combining together your SQL query, and SORT step, get really familiar how the SORT step works, using some verified input test data, rather than those received from DB2. When all SORT issues are resolved, then start to combine both of your steps together, carefully verifying all your data at each step!