Posted: Tue Apr 19, 2022 1:03 pm Post subject: Pulling one line of output multiple lines
I have a requirement to run a -DISPLAY BUFFERPOOL command in DB2 and capture ALLOCATED value.However the output displays two times ALLOCATED = and I am interested in capturing the first line output and I need in the below format and I want to create a comma separated csv file.
I need the value under DSNB402I message and not under DSNB431I
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Tue Apr 19, 2022 6:44 pm Post subject:
shekar123,
You do NOT need multiple steps to gather the information that you need. Here is DFSORT JOB that will give you the desired results. It gives you more information than you need.
... can add "n" number of Display bp commands for different systems
END
/*
//**********************************************************
//* Extract the Buffer pool information and create a CSV *
//* file using DFSORT *
//**********************************************************
//EXTINFO EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=&SYSUID..BUFPOOL.DISPLAY.OUTPUT
//SORTOUT DD DSN=&SYSUID..BUFPOOL.REPORT,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(1,1),RLSE)
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=(02,08,CH,EQ,C'DSNB401I'),
PARSE=(%01=(STARTAFT=C'-', # Db2 system name
ENDBEFR=C' ',
FIXLEN=8),
%02=(STARTAFT=C'NAME ', # Buffpool name
ENDBEFR=C',',
FIXLEN=4),
%03=(STARTAFT=C'ID ', # Buffpool ID
ENDBEFR=C',',
FIXLEN=4),
%04=(STARTAFT=C'COUNT ', # Buffpool use count
ENDBEFR=C' ',
FIXLEN=8)),
OVERLAY=(134:%01,
143:%02,
148:%03,
153:%04)),
IFTHEN=(WHEN=(14,09,CH,EQ,C'ALLOCATED'),
PARSE=(%05=(STARTAFT=C'=', # Allocated value
ENDBEFR=C' TO',
FIXLEN=15)),
OVERLAY=(162:%05))
Thank you so much for your help. I tried my best from my end but could not. Amazing solutions.
The DISPLAY report came perfect
Organization . . . : PS
Record format . . . : FBA
Record length . . . : 133
Block size . . . . : 27930
The REPORT dataset is
Organization . . . : PS
Record format . . . : FBA
Record length . . . : 80
Block size . . . . : 27920
and I get the output as
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
B2 SYSTEM NAME,BP NAME,BP ID,BP USAGE COUNT,ALLOCATED
0000
21800
28000
37000
Seems OUTREC has truncated data based on the position being extracted.
Another thing - if I want to copy the data to a spread sheet / download the file to PC and use xcel to open the file , should I create a tab delimited file instead of csv file format. What should be the MID value to be used.
Basically I want to store the BUFFER POOL data in a spread sheet. _________________ Shekar
Grow Technically
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Tue Apr 19, 2022 11:26 pm Post subject:
shekar123,
Code RECFM=FB for SORTOUT and rerun the job. The report is already created as .csv file, so you can download the file to pc and save it as bpreport.csv and open it excel. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
First of all extremely sorry . This is a new LPAR and new access setup.I did not check what SORT product is being used.I was in assumption that DFSORT is being used . I now see that
SYNCSORT FOR Z/OS 3.1.2.0R
is being used and I have coded SORTOUT dataset with RECFM=FB and I get the output.
Could be that difference in both products code is yielding different results.
Organization . . . : PS
Record format . . . : FB
Record length . . . : 80
Block size . . . . : 27920
WER108I SORTIN : RECFM=FBA ; LRECL= 133; BLKSIZE= 27930
WER073I SORTIN : DSNAME=TEST.BUFPOOL.DISPLAY.OUTPUT
WER257I INREC RECORD LENGTH = 176
WER238I POTENTIALLY INEFFICIENT USE OF INREC
WER237I OUTREC RECORD LENGTH = 187
WER110I SORTOUT : RECFM=FB ; LRECL= 80; BLKSIZE= 27920
WER074I SORTOUT : DSNAME=TEST.BUFPOOL.REPORT
WER410B 5,916K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16-MEGABYTE LINE,
WER410B 0 BYTES RESERVE REQUESTED, 2,253,903 BYTES USED
WER405I SORTOUT : DATA RECORDS OUT 0; TOTAL RECORDS OUT 6
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER416B SORTIN : EXCP'S=1,UNIT=3390,DEV=1B6B,CHP=(1011121316171514,1),VOL=P3
WER416B OUTFIL WAS USED FOR SORTOUT
WER054I RCD IN 126, OUT 126
WER169I RELEASE 3.1 BATCH 0541 TPF LEVEL 2.0
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Wed Apr 20, 2022 6:22 pm Post subject:
shekar123,
The Control cards work fine for DFSORT. As a DFSORT developer I cannot debug the other competitive product.
Make sure the output from DISPLAY BUFFERPOOL is having the values in the right position. Since the output is FBA. Browse the dataset and issue the command
Code:
DISPLAY CC and press enter
This will display the carriage control character and you can see the right positions.
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+--
1READY
DSN SYSTEM(DB2T)
DSN
-DISPLAY BUFFERPOOL(BP0)
DSNB401I -DB2T BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 164
DSNB402I -DB2T BUFFER POOL SIZE = 20000 BUFFERS AUTOSIZE = NO
VPSIZE MINIMUM = 0 VPSIZE MAXIMUM = 0
ALLOCATED = 20000 TO BE DELETED = 0
The parse is for looking for DSNB401I at position 2 for a length of 8 and ALLOCATED at position 14 for a length of 9. So verify that those positions are right _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Thank you for your help and I understand the concern. I am trying my best to get this done and somehow either I am missing the positions / getting one field and missing another field. I did try various positions.
To make it simple I am just extracting BPNAME and ALLOCATED and the dataset properties are
Code:
BUFPOOL.DISPLAY.OUTPUT
Organization . . . : PS
Record format . . . : FBA
Record length . . . : 133
Block size . . . . : 27930
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
1READY
DSN SYSTEM(DB2P)
DSN
-DISPLAY BUFFERPOOL(BP0)
DSNB401I #DBP1 BUFFERPOOL NAME BP0, BUFFERPOOL ID 0, USE COUNT 294
DSNB402I #DBP1 BUFFER POOL SIZE = 30000 BUFFERS AUTOSIZE = NO
VPSIZE MINIMUM = 0 VPSIZE MAXIMUM = 0
ALLOCATED = 30000 TO BE DELETED = 0
IN-USE/UPDATED = 167 OVERFLOW ALLOC = 0
DSNB431I #DBP1 SIMULATED BUFFER POOL SIZE = 0 BUFFERS -
ALLOCATED = 0
IN-USE = 0 HIGH IN-USE = 0
SEQ-IN-USE = 0 HIGH SEQ-IN-USE = 0
DSNB406I #DBP1 PGFIX ATTRIBUTE -
CURRENT = YES
i have also typed in DISPLAY CC and I get the above format
My analysis
1.
Code:
INREC IFTHEN=(WHEN=(02,08,CH,EQ,C'DSNB401I'),
PARSE=(%01=(STARTAFT=C'NAME ', # BUFFPOOL NAME
ENDBEFR=C',',
FIXLEN=6)),
OVERLAY=(134:%01)),
Data starts at position 2
For parsed field %01 I have given FIXLEN=6 because bufferpool name can have max 6 bytes like BP32K9
So we are overlaying bufferpool name at pos 134 and it is of 6 bytes and ending at pos 139
2.
Code:
IFTHEN=(WHEN=(14,09,CH,EQ,C'ALLOCATED'),
PARSE=(%02=(STARTAFT=C'=', # ALLOCATED VALUE
ENDBEFR=C' TO',
FIXLEN=10)),
OVERLAY=(141:%02))
Data starts at position 14
For parsed field %02 I have given FIXLEN=10 because ALLOCATED value can be maximum 10 bytes
So we are overlaying allocated value at pos 141 leaving a space at pos 140 and it is of 10 bytes and ending at pos 150
we are grouping recorda with begin for DSNB401I and ending for ALLOCATED at pos 134 with length of 17
( 6 bytes for bufferpool name + 1 byte space at pos 140 + 10 bytes for ALLOCATED value ) and also we are adding non blank value at pos 180 and length of 8 bytes and it will end at pos 187
We are now finally creating an output file with removing CC characters , without any detail and including data only at pos 180 which is non blank and to have header as
BPNAME,ALLOCATED
SECTIONS=(180,01) to repeat when group of records BEGIN and END are processed
And finally I am not getting desired result
Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
********************************* Top of Data **********************************
BPNAME,ALLOCATED
BP0
BP1
BP2
BP3
BP4
BP5
BP6
BP7
BP8
BP9
BP10
BP11
BP12
BP13
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Fri Apr 22, 2022 7:12 pm Post subject:
shekar123,
why are you pushing 17 bytes when you just had BUFFERPOOL name which is only 6 bytes. By doing that you are overlaying the ALLOCATED VALUE which is at position 141.
The PUSH on OUTREC is
Quote:
PUSH=(134:134,17,
it should be
Code:
PUSH=(134:134,06,
That should give you the desired results. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Thank you so much Kolusu for your help and detaild explanation. I was able to get my resilts after changing as per your suggestion.I just made adjustments to SQZ for character and SQZ for numbers.
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Sat Apr 23, 2022 4:10 am Post subject:
shekar123 wrote:
I was able to get my resilts after changing as per your suggestion
shekar123,
Glad to hear that you are able to get the results.
shekar123 wrote:
I just made adjustments to SQZ for character and SQZ for numbers.
why do you need to do that? You do realize that you basically wrote the fields AS IS? Since your intention is to send this file to excel , you don't have to align them at all .
Btw you just needed to change 1 statement in my original solution.
ie. this statement ( the db2 system name starts with a # sign in your output where as my output started with hyphen "-"
Code:
PARSE=(%01=(STARTAFT=C'-', # Db2 system name
to
Code:
PARSE=(%01=(STARTAFT=C'#', # Db2 system name
and now you should get the desired results without changing any thing.
Now download the report to the PC and save it as .csv file and then open the PC file with EXCEL and now everything is aligned into separate columns.
You just needed 1 character change in my solution, that's all _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Superb explanation.I just copied your code not realizing the DB2 sub system character recognization could matter here.I am very clear now. Here is your original code just changing the DB2 CRC and the desired results.
Code:
OPTION COPY
INREC IFTHEN=(WHEN=(02,08,CH,EQ,C'DSNB401I'),
PARSE=(%01=(STARTAFT=C'#', # DB2 SYSTEM NAME
ENDBEFR=C' ',
FIXLEN=8),
%02=(STARTAFT=C'NAME ', # BUFFPOOL NAME
ENDBEFR=C',',
FIXLEN=4),
%03=(STARTAFT=C'ID ', # BUFFPOOL ID
ENDBEFR=C',',
FIXLEN=4),
%04=(STARTAFT=C'COUNT ', # BUFFPOOL USE COUNT
ENDBEFR=C' ',
FIXLEN=8)),
OVERLAY=(134:%01,
143:%02,
148:%03,
153:%04)),
IFTHEN=(WHEN=(14,09,CH,EQ,C'ALLOCATED'),
PARSE=(%05=(STARTAFT=C'=', # ALLOCATED VALUE
ENDBEFR=C' TO',
FIXLEN=15)),
OVERLAY=(162:%05))
By your help I was able to generate values for report BPNAME,ALLOCATED
I need your help further please. I have to generate report with following fields and further more based on DB2 DISPLAY commands for group bufferpools and should be comma seperated.
Code:
CURRENT RATIO - FROM -DISPLAY GROUPBUFFERPOOL(GBP0) GDETAIL(*)
SUGGESTED RATIO - I WANT TO HARD CODE 5
BP DBP1 - FROM -DISPLAY GROUPBUFFERPOOL(GBP0) MDETAIL(*) AGAINST
DBP1 MEMBER
BP DBP2 - FROM -DISPLAY GROUPBUFFERPOOL(GBP0) MDETAIL(*) AGAINST
DBP2 MEMBER
TOTAL LOCAL - SHOULD BE ADDED UP FROM VALUES DBP1 AND DBP2
DIR ENTRIES FOR - ABOVE SUM/2
LOCAL PAGES
GDETAIL(*) will give current ratio
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
CURRENT DIRECTORY TO DATA RATIO = 11
So basically a report with values from different DB2 commands should be generated and BP DBP1 is against one member and BP DBP2 is against another member in data sharing.
So a CSV file with below headings and values
BPNAME,ALLOCATED,CURRENT RATIO,SUGGESTED RATIO,BP DBP1,BP DBP2,TOTAL LOCAL BUFFERPOOLS,DIRECTORY ENTRIES FOR LOCAL PAGES
I would request you to kindly help with this requirement on share high level steps as I guess I have to generate different files for each command and then use JOIN to join records with matching condition and build portion of records from each file to generate the report. _________________ Shekar
Grow Technically
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Mon Apr 25, 2022 11:35 am Post subject:
shekar123 wrote:
So a CSV file with below headings and values
BPNAME,ALLOCATED,CURRENT RATIO,SUGGESTED RATIO,BP DBP1,BP DBP2,TOTAL LOCAL BUFFERPOOLS,DIRECTORY ENTRIES FOR LOCAL PAGES
I would request you to kindly help with this requirement on share high level steps as I guess I have to generate different files for each command and then use JOIN to join records with matching condition and build portion of records from each file to generate the report.
shekar123,
I have NO idea where the columns in BOLD are being picked up. As is I do NOT have access to the DISPLAY GROUPBUFFER pool output. So you need to show the RAW output of the command as is and explain the columns that you need to pick.
If you can send the output of the group bufferpool command as a text file it would be helpful. Send it offline to my email address.
Thanks _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
