View previous topic :: View next topic
|
Author |
Message |
mfstudent1 Currently Banned New User
Joined: 03 Mar 2006 Posts: 18
|
|
|
|
Hello ,
Iam a new member to the group...
I have to move all the distinct values of a table into a dataset and then move the dataset into an excel sheet.
Can I do that??
If yes,Can you please help me with the pgms to do so??? |
|
Back to top |
|
|
amitava Warnings : 1 Active User
Joined: 30 Oct 2005 Posts: 186 Location: India
|
|
|
|
Hey mfstudent1, u can use the following JCL - which will unload the DB2 table to a dataset and then the file u move by ISPF 6 option - File Transfer. Eg :-
Suppose you want to unload the rows for
department D01 from the project table. You can fit the table
specification on one line, and you do not want to execute any non- SELECT statements, so you do not need the SQL parameter. Your
invocation looks like this:
Code: |
//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DSN=DSN8UNLD.SYSREC00,
// UNIT=SYSDA,SPACE=(32760,(1000,500)),DISP=(,CATLG),
// VOL=SER=SCR03
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8710.PROJ WHERE DEPTNO='D01' |
Now just move the DSN8UNLD.SYSREC00 DS by FILE TRANSFER option.
But in this case, when u will import this file in a XLS, it will not look very good.
THat's why I suggest u to write a simple COBOL code which will read the specified table, opens a cursor, fetch it and then writes these data into the DS with '~' seperated. So when u transfer the output file, the corrosponding file in ur system will be '~' seperated. Now u import this file to XLS using the seperator. |
|
Back to top |
|
|
superk
Global Moderator
Joined: 26 Apr 2004 Posts: 4652 Location: Raleigh, NC, USA
|
|
|
|
Isn't it easier and less time-consuming to use your third party data management tools (Mercator, Cloverleaf, Gentran, SAS, Crystal) to do all this instead? |
|
Back to top |
|
|
pspreddy
New User
Joined: 14 Mar 2006 Posts: 24 Location: Chicago
|
|
|
|
Hi mfstudent1,
Two simple options for this
1) You can write a SPUFI to get the results into a dataset directly and then you can ddownload this data into an Excel sheet. Use option 6 as said. And open the downloaded file using excel and go for 'Fixed Width' option where you can specify the width of each and every column that you need. The data looks very neat. If you have any unwanted rows in the datset like
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
you can either delete them in the dataset itself before downloading or you can delete them from the excel sheet that you generate.
2) You can use QMF to run the query and say 'export ?' to specify the dataset you want to capture the results into. then download this dataset as said above
I generally opt for SPUFI rather than QMF. SPUFi is very convenient to use and we can always go back to the dataset whenever we want and with all sort of edit options. But with QMF you may not be able to retain the data unless you specificaly exort it.
We can come out of SPUFI and go to dataset to check the results. I heard QMF holds the table while we are viewing the results. in simple terms you can compare QMF to online and SPUFI to batch task. Some one please correct me if I'm wrong.
Have a great weekend folks
-Shiva |
|
Back to top |
|
|
martin9
Active User
Joined: 01 Mar 2006 Posts: 290 Location: Basel, Switzerland
|
|
|
|
hy mfstudent1,
yes you can make a file for excel,
but you have to know, that you need
to create a simple sequential dataset,
ergo a flat file.
just, and this is important, you must do it
as a csv file...
value1 ; value2 ; value3 ; valuen
you see that each record should separate its values
by any delimiter. each value gives then a column
in excel data row.
martin9 |
|
Back to top |
|
|
prasanth_thavva
New User
Joined: 28 Jul 2005 Posts: 86 Location: Chennai
|
|
|
|
HI pspreddy,
thank you for your kind information, i need more differences for SPUFI and QMF, please let me know..
Thanks and Regard,
venkata Prasanth
Infy |
|
Back to top |
|
|
pspreddy
New User
Joined: 14 Mar 2006 Posts: 24 Location: Chicago
|
|
|
|
Hi Prasanth,
Some of the differences are like below
1) Using SPUFI, we can run multiple SQL statements at the same time. But this is not possible in QMF. A QMF query can only contain one SQL statement. Even if it has a second SQl statementon the panel, it ignores that running only the first statement
2) QMF queries can include dynamic variables, in which QMF prompts the user for a value and then executes the query based upon the values supplied. But this is not possible in SPUFI
3) QMF queries can be shared across users. We can share queries using SPUFI also. But the difference is when we use SPUFI all the queries will be stored as members of the the PDS you mention in the SPUFI panel with the name you give
4) I heard QMF also can be run in batch. But no idea on this.
5) The output of a SPUFI will be in a dataset preceded by your user id. In some organizations another user can not view the contents of a dataset starting with your user id (We have this constraint)
6) Using SPUFI there is a limit on the number of records (I do not remeber the exact count limit) that can be fetched with an SQL statement. If the record count excees this limit SPUFI can not fetch those records. But using QMF you can fetch as many reords as you want and you can direct them to a dataset if you wish to
These are the differences I know of. May be site gurus can throw some light on this topic.
Thanks
Sivaprasad |
|
Back to top |
|
|
KS
New User
Joined: 28 Feb 2006 Posts: 91 Location: Chennai
|
|
|
|
Hi,
Write the query in the spufi panel.
Then save the report to the dataset using the command - EXPORT REPORT TO NAME;
Now output of the query will be stored in the dataset ur ID.NAME.REPORT
Now using the FTP option 6 u can send the report to Excel.
Please correct if am wrong.
Thanks,
KS |
|
Back to top |
|
|
jasorn Warnings : 1 Active User
Joined: 12 Jul 2006 Posts: 191 Location: USA
|
|
|
|
I usually see the csv method used for this but I have used another method that works pretty well. Actually, I use this method from cobol or other apps that produce plain text files when I want a fancy report. This method gets cool points if you create the spreadsheet and actually email it from the mainframe. I first tried this with to create reports for a football pool a friend of mine used to run(I do now) back when excel added the html converted thingie to it's functionality. This process sounds complicated but it's really not. In fact it can be a real time saver if you have a mainframe process for which you need to repeatedly create a fancy report. Here is the basic process:
1 Create the file the way you want it to look in excell. Could be basically anything you like from more of a fancy report to something more complicated. As long as you can save it to html from within excel, this will work.
2 If this the file will basically be a static size, fille in the data fields with place holder dummy values. I used something like V_FNAME.
3 If this fits the format of a header, body(detail rows), and footer, then just make one row of data using the dummy values.
4. Save the spredsheet as html. Later the file can be renamed to .xls and although is has html or xml or whatever, the xls extension prevents it from being opened in a browser. On the other hand, if you'd prefer your audience to use a browser leave it as html.
If #3 was true,
5. edit the html and put in comments that mark the begging and ending of your data row. So the body is separated from the header and footer. Actually it might be better to separate them into three files.
6. Modify your program so that it creates a duplicate of the 'body' file above.
7. Concatenate the 3 files and put the result wherever you want.
8. You might also want to process the header and footer separately if they contain things that would normally change such as date or something.
else if #3 above is false and this report is more of a static format like that in my attachment,
9. Read the file with the dummy values and replace them with real values. I usually do this be creating arrays for each of the dummy value 'types' and then read each row and replace values as a appropriate. I have other methods of doing this but this is the one I use most.
That's basically it. For cases in which you need just a simple spreadsheet, you would most likely stick to the easy csv mothod.
Using this same approach I create the attachment in dbase vi in a jiffy. I've also used this method on the mainframe so create more professional looking reports that are in spreadsheet form that get sent straight to the people who need to work with them. This is nice because there is absolutely nothing they need to do before using the spreadsheet.
I'm in linux so the attachments won't actually be of excel. Instead I'll post one opened in firefox and one opened in openoffice. Openoffice doesn't look as good as the excel since I created this in excel. The same approach could be used with oo though. |
|
Back to top |
|
|
parikshit123
Active User
Joined: 01 Jul 2005 Posts: 269 Location: India
|
|
|
|
Hi jasorn,
Very valuable suggestions.
Thanks for that. |
|
Back to top |
|
|
jasorn Warnings : 1 Active User
Joined: 12 Jul 2006 Posts: 191 Location: USA
|
|
|
|
parikshit123 wrote: |
Hi jasorn,
Very valuable suggestions.
Thanks for that. |
Sure thing.
Using excel as a 'report painter' this way has served me well. This is especially true in shops which don't have a tool for this purpose. Most places have at least one office product that you can use for this purpose but I haven't seen many that have a product specifically for creating reports that as flexible and easy as this simple, nothing else to buy, method.
You could use this method with any product that uses a plain text format like xml which ms office uses now. I don't think it would have worked too well in the old format. The fact that word and excel also creat html is a bonus. And it's an extra, extra bonus that you can rename it's .html output to .xls and it opens just fine.
Normally I wouldn't recommend either for creating html files but it's a workable solution in this case.
For those who want to implement this but needs some clarification on my long, convaluted post, let me know and we can talk it through.
I'd be interested too in hearing from those who alread have or do in the future use this method. |
|
Back to top |
|
|
jasorn Warnings : 1 Active User
Joined: 12 Jul 2006 Posts: 191 Location: USA
|
|
Back to top |
|
|
jasorn Warnings : 1 Active User
Joined: 12 Jul 2006 Posts: 191 Location: USA
|
|
|
|
Also, the sylk format as pointed out in that post is pretty good for this general purpose and might be easier for folks who want to format the output a bit and for whatever reason rules out the other options.
I think the bottom line is there is a pretty easy method for creating snazzy reports and spreadsheets and sending them straight to a pc user for use with no prep work for the end user. And perhaps no reason for many of use to use those fancy tools and add ons that can be a little pricey. |
|
Back to top |
|
|
|