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

DSNTIAUL and DB2 scalar functions


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Taranprietsingh

New User


Joined: 25 Nov 2021
Posts: 2
Location: UK

PostPosted: Thu Nov 25, 2021 1:52 am
Reply with quote

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
------------------------------------------------------------------------------
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1231

PostPosted: Fri Nov 26, 2021 9:18 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Taranprietsingh

New User


Joined: 25 Nov 2021
Posts: 2
Location: UK

PostPosted: Fri Nov 26, 2021 11:17 pm
Reply with quote

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.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1231

PostPosted: Sat Nov 27, 2021 1:05 am
Reply with quote

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:
Code:
//SYSREC00 DD DSN=&&TEMPDATA,RECFM=FB,LRECL=r_size,
and in the next step convert RECFM=FB to RECFM=VB using any utility, from IEBGENER to SORT, or others.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2815
Location: NYC,USA

PostPosted: Tue Nov 30, 2021 6:11 pm
Reply with quote

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.
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 -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Trying to use sort to do two function... DFSORT/ICETOOL 14
No new posts SUBSTR for Date functions DB2 4
No new posts Regd Date Functions in Syncsort SYNCSORT 2
No new posts Unload from Table with DSNTIAUL delim... DB2 1
No new posts Can you suppress NULLIF char for DSNT... DB2 1
Search our Forums:

Back to Top