Posted: Wed Apr 26, 2017 10:04 am Post subject: Append variable number of lines to the correct previous one
Hello,
maybe this one can be done with JOINKEYS processing the same file twice, but I still can't get any good result. I'm reading manual trying to understand how I can do it. Meanwhile if I can get a quick support, well I'll be grateful, no problem otherwise.
Often we have some spot DB2 queries to run, sometimes these queries might be too heavy in results for QMF, so we run them via Batch using this tool
Code:
//GO1 EXEC DB2GO
//STEPLIB DD DISP=SHR,DSN=DB2.XXXX.RESLIB
//SYSTSIN DD *
DSN SYSTEM(XXXX)
RUN PROGRAM(PROG2) PLAN (PLAN2) -
LIB ('DB2LIB.RUNLIB.LOAD')
The output from previout tool is something like this (if resulting line is too long, it is splitted in more lines, each line can be identified by it's number, so 1_, 2_ and so on)
output is variable according to query complexity in terms of column selected, so fields are NOT always in the same position ( second 1_ might start at different column from the above example). Also usually a line is splitted in two chunks, but it could be splitted in three parts too or maybe more, we can't know in advance. Anyway we can be sure that the 2nd line 1_ need to attached to the end of the previous 1_, 2_ at the end of the previous 2_ and so on. I have also some difficult to understand how I can dinamically calculate in advance the width of the new fille I would like to create with sort, at the moment I allocate a 1000 bytes long, which I guess will be enough.
So in output we would need something like this:
Code:
+---------------------------------------------------------------------------------------------------------------------------------------
! AAAAAA ! BBBBBBB ! CCCCCCC !! DDDDDDDDDDDDDDDD ! EEEEEEEEEEEEEEE !
+---------------------------------------------------------------------------------------------------------------------------------------
XX 1050405 000000000000893994 TTTTTTTTTT TTTTTTT TTTTTTT GG.MM.AAAA
XX 1054016 000000000000909742 TTTTTTTTTT TTTTTTT TTTTTTT GG.MM.AAAA
XX 1048799 000000000000991566 TTTTTTTTTTTTTT TTTTTTTT GG.MM.AAAA
XX 1048355 000000000000686623 TTTTTTTTTTTTTT TTTTTTTT GG.MM.AAAA
XX 1047049 000000000000860967 TTTTTTTTTT TTTTTTT TTTTTTT GG.MM.AAAA
XX 1047096 000000000000930578 TTTTTTTTTT TTTTTTT TTTTTTT 22.09.2014
XX 1052489 000000000000884280 TTTTTTTTTT TTTTTTTTTT 22.09.2014
... other lines
XX 1064278 000000000000240283 TTTTTTTTTT TTTTTTTTTT 07.11.2014
Actually I do this manually with ISPF command lines, and it's rather time consuming to me, I would like to process the output file from the tool to gain the previous output ready to be imported in an EXCEL spreadsheet. Hope in some quick help, which will be greatly appreciated.
Thanks in advance.
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Wed Apr 26, 2017 10:23 am Post subject:
Fab,
You can use DSNTIAUL to unload the records from the DB2 in raw format. You have couple of options to reformat the data to readable format.
1. Change the sql for the columns to a readable format (Decimal, Integer, float...)
2. Run the Unload file thru an utility to reformat.
or if your shop has High performance Unload (HPU) then you can use to unload the records from the DB2 table with excel delimiters. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Kolusu,
thanks, but as far as I know we are only allowed to use that tool I told you in my post. But you can be sure that I will ask if we can use that tool too. These datas are directly from our production environment and here sysops are very paranoid about what we can or can't do with that data. I work in a big Bank, probably you can imagine. Thanks as usual for you continuous support.
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Wed Apr 26, 2017 11:49 am Post subject:
Fab wrote:
Kolusu,
thanks, but as far as I know we are only allowed to use that tool I told you in my post. But you can be sure that I will ask if we can use that tool too.
You are running DSNTEP2/QMF to generate the report. Check this link for an example of DSNTIAUL.
Give the example a try and see if it works for you.
Fab wrote:
These datas are directly from our production environment and here sysops are very paranoid about what we can or can't do with that data. I work in a big Bank, probably you can imagine. Thanks as usual for you continuous support.
You are reading the data the same way. One in the raw format and other in formatted form which is readable.
Btw if you insist on merging the datasets then it can be done, but it would require a lot of processing. You need to use IFTHEN=(when=GROUP processing but you need to able to distinguish as how many records it being split into.
For example if your data ends up less than 266 bytes ,then it is only 2 groups, but if the data is 1500 bytes then you end up with more than 11 groups. so you need to adjust as to where each group goes, like group 2 starts at position 134 and group 3 starts at 267 and so on... _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You are reading the data the same way. One in the raw format and other in formatted form which is readable.
I don't think so. I can't create or execute any jcl by hand in production, so no unload is possible. I can just run queries and export results from QMF.
Let me explain the environment we have and what we do right now. As I told sometimes QMF can't manage our complex queries, insufficient resouces. Then we put that query in a catalogued dataset. Every 15 min a scheduled process execute those commands (we use it for spufi). I have no control on that process, and no control on jcl which is already there in that form. We have at least 2 different environment here. Production which is read only, and test which we can modify. Production and test DB2 database are NOT aligned nor can be. So I have to execute my query in production, transfer sysout to test environment and there process it creating an exportable EXCEL file.
I can process it by hand (ISPF), with a jcl or a program.
you mean I should try that sort (modified of course) and see if it fits my needs? Ok I will
Quote:
Btw if you insist on merging the datasets then it can be done, but it would require a lot of processing. You need to use IFTHEN=(when=GROUP processing but you need to able to distinguish as how many records it being split into.
I guess that 7 split are enough for our common needs, in particular cases I would need to adjust that hypothetical sort. but as I understand you maybe this time is a bit too complex to be done, at least by my limited knowledge.
Thank you very much as usual Kolusu.
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Thu Apr 27, 2017 12:17 pm Post subject:
Fab,
You sure do have a weird set up at your shop. Since you can run jcl's I would suggest trying out the sample JCL for DSNTIAUL as shown in my link earlier in your test environment.
Also check this link for converting the numerical fields to display fields and generating the SQL from it using sysibm.syscolumns
You can change the above sql to append any excel delimiter value and you use that generated SQL as input to DSNTIAUL which would give you the desired results.
I can show you how to do in DFSORT but I for one believe in using the right tool for the right job. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Hi Kolusu,
weird set up yes, even frustrating, nonetheless that's it. I can't follow your suggestions because test DB2 database is absolutely NOT reliable. I need production data. And I can't request any database alignment for two good reasons, burocracy and other people using test environment for their own develpment purpose. Burocracy also make me reluctant to create a new jcl and run it in test, in production we are not allowed to create or transfer JCL, we can only ask another group to create that for us, giving specification. So it's pointless to prepare something new in test and ask for a new creation in production, because it will be created according our production JCL standard, and I doubt it will be the same as I want. I have to process production data, transfer them to test and then process it there. Where process might be to use ISPF, a sort or a program.
Have no other choice here. If you can help me with sort would be wonderful, but I understand you if you don't absolutely no problem anyway. Thanks so much Kolusu.
DSNTLA4 (in Swedish, Landscape is called Liggande) will format it as Landscape, thus
(this should be shown using Courier New, but the forum doesn't give me that luxury, but you get (?) the idea))
Joined: 01 Feb 2007 Posts: 1075 Topics: 7 Location: At Home
Posted: Mon May 01, 2017 4:07 am Post subject:
The code tags give you a fixed pitch font - same as Courier New. OK, you get green on black but that is what is probably being seen anyway - before printing. _________________ Utility and Program control cards are NOT, repeat NOT, JCL.
because in 1st case it will be perfect, I'm not sure I can execute REXX scripts in my test environment tho, I will verify tomorrow morning.
I tried a couple of sort but I couldn't get desired results yet, I see no answer from Kolusu so I think that maybe I could be misunderstood his answer above. I understand that formatting output straight from the Batch query is the way to go, but as I tried to explain I simply can't create/execute anything in production environment, and every JCL that we want to transfer in production must be approved by our sysops before (this is what I called burocracy). I can only get sysout from production tool execution and process it with easy in my test environment. I was in doubt I did not explain me good above, I'm sorry if I didn't.
Joined: 02 Dec 2002 Posts: 620 Topics: 173 Location: Stockholm, Sweden
Posted: Tue May 02, 2017 4:30 pm Post subject:
Fab
The results you get are dependent on whether you choose DSNTLA4 (the first set of results) or DSNTSA4 (the second set )
You SHOULD be able to run a text script even if it means you having to do a manual ALTLIB on the library where the script is. _________________ Michael
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Tue May 02, 2017 6:53 pm Post subject:
misi01 wrote:
DSNTLA4 (in Swedish, Landscape is called Liggande) will format it as Landscape, thus
(this should be shown using Courier New, but the forum doesn't give me that luxury, but you get (?) the idea))
In the same way, DSNSA4 (S = Swedish for portait) would give you something along these lines
The results you get are dependent on whether you choose DSNTLA4 (the first set of results) or DSNTSA4 (the second set )
You SHOULD be able to run a text script even if it means you having to do a manual ALTLIB on the library where the script is
Misi01,
From your brief description (portrait vs landscape), I don't think it will work for what Fab is trying to do.
What is the maximum LRECL that your rexx exec can handle for landscape mode?
For example, SYSIBM.SYSTABLES have 59 columns and the formatted output from DSNTEP2 will be around 21 pages @ 133 byte Lrecl for just 1 row of data. Now these 21 pages need to be combined into single page which would result in a single record of 2793 bytes in Lrecl. I may be wrong but I don't think your exec can handle that LRECL. Also remember that DSNTEP2 has a weird way of formatting the data.
For example you can try this and look at where you would find 2nd page of your 1st column data ( Hint : look for .1 in position 11)
Code:
//STEP0100 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM(DB2P)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
END
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD DSN=Misi01.DSNTEP2.OUTPUT.FETCH20K,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(100,50),RLSE)
//SYSIN DD *
SELECT *
FROM SYSIBM.SYSTABLES
FETCH FIRST 20000 ROWS ONLY
;
Now try to format that using DSNTLA4 or DSNTSA4 and see if you can ALL the columns into single record. (roughly about 24 pages with headers)
If your exec does indeed format the data, I would love to have that exec.
Joined: 26 Nov 2002 Posts: 12370 Topics: 75 Location: San Jose
Posted: Tue May 02, 2017 7:07 pm Post subject:
Fab wrote:
I tried a couple of sort but I couldn't get desired results yet, I see no answer from Kolusu so I think that maybe I could be misunderstood his answer above.
Fab,
As I mentioned earlier, you can format it using DFSORT however it would need about 3 passes of data which includes 2 sorts which I was trying to avoid. As I was I busy wrapping some of my work, I did not get a chance to work on it.
Here is a way to do it with DFSORT. It would give you just the data without the headers.
P.S: I may have missed something as I do not have a working unload data from DSNTEP2. I just used the SYSIBM.SYSTABLE as a sample with 18000 rows. I do not handle more than 99,999 pages of output from DSNTEP2. So you may have to change the sort solution a bit, if you have more than 100K pages of data which I will leave it to you.
Last but not least, I still stand by using DSNTIAUL and get the raw data and then format it. I am aware that you have restrictions but, I am sure if you are allowed to run DSNTEP2 you would be able to run DSNTIAUL.
Code:
//*************************************************************
//* TAG THE RECORDS TO GROUPS AND SORT BASED ON GROUP NUMBER **
//*************************************************************
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=Your unload from DSNTEP2 file
//*
//SORTOUT DD DSN=&&TMP,DISP=(,PASS),SPACE=(CYL,(100,25),RLSE)
//SYSIN DD *
INCLUDE COND=(001,005,CH,EQ,C'1PAGE',OR,
001,133,SS,EQ,C'_|')
Joined: 02 Dec 2002 Posts: 620 Topics: 173 Location: Stockholm, Sweden
Posted: Tue May 02, 2017 11:42 pm Post subject:
Kolusu, you're quite right inasmuch as the file with the results HAS to have been (pre) allocated with a record length large enough to accommodate the landscape folded results (the script will issue an error message if it's not)
You're also right that DSNTEP2 produces some weird results (I'd be more inclined to call them bugs since the resulting layouts aren't consistent). My rexx script takes a number of these into account.
Be that as it may, fab has two possible solutions to his problem; it's up to him you choose which path he takes. _________________ Michael
I'm sorry but it is still unclear to me what are DSNTLA4 and DSNTSA4.
@Kolusu
I apology if I gave you impression that I was insisting on my intention to use a sort. As you can easily guess english in not my native language and I could express things in a wrong way sometimes.
Code:
Last but not least, I still stand by using DSNTIAUL and get the raw data and then format it. I am aware that you have restrictions but, I am sure if you are allowed to run DSNTEP2 you would be able to run DSNTIAUL.
you are right, we can execute that, but we can't write JCL and transfer them in production as they are. We can write and run JCL in test, and no more than that. When we must create some new JCL in production we have a complex request to compile, in which we must describe what our JCL must accomplish. But JCL will be entirely written in production by our sysop team according to our JCL standard (which is rather basic). Just to (try) to be even more precise I can tell you that in my 1st post here you gave me a smart solution to make a formula calculation in a JCL I had. Well, when I requested to transfer that JCL that I had succesfully run in test env, they refused to write it as it was in production because they could not understand what that JCL did. So I was forced to find a "trasversal" (read "almost illegal")solution to execute that JCL in production. It would be difficult to explain even in my native language.
Code:
I may have missed something as I do not have a working unload data from DSNTEP2. I just used the SYSIBM.SYSTABLE as a sample with 18000 rows. I do not handle more than 99,999 pages of output from DSNTEP2. So you may have to change the sort solution a bit, if you have more than 100K pages of data which I will leave it to you.
Understood, maybe I have to modify something right now. because when I try your code I get this:
Code:
ICE600I 0 DFSORT ICETOOL UTILITY RUN STARTED
ICE650I 0 VISIT http://www.ibm.com/storage/dfsort FOR ICETOOL PAPERS, EXAMPLES
ICE632I 0 SOURCE FOR ICETOOL STATEMENTS: TOOLIN
ICE630I 0 MODE IN EFFECT: STOP
RESIZE FROM(DAF) TO(OUT) -
TOLEN(00000)
$
ICE604A 0 ERROR IN KEYWORD, PARAMETER, OR DELIMITER
ICE602I 0 OPERATION RETURN CODE: 12
My test input is actually 083221 lines for 1415 pages so probably that is not the problem. Tomorrow I will see how it works Icetool/resize token to solve this error. Thanks for you help & support Kolusu.
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