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

Extract Report using SPUFI/QMF


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

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sat May 02, 2009 3:14 am
Reply with quote

Hi all,

I would like to have an extract format of output from my SPUFI/QMF result data set.
is there any way to acheive this .. ??

By extract i mean the file which is ready to load using some Db2-fileaid 3.7 option or db2 load utilities ... and not in the readable char format .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat May 02, 2009 4:20 am
Reply with quote

why don't you use the sql with UNLOAD to extract from your database?
then everything would be 'loadable'.
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sat May 02, 2009 4:31 am
Reply with quote

Thanks dbzTHEdinosauer for the Reply!!

could you be please increase clarity ??

do you mean run to my query in a JCL and unload it to a dataset ??
if so i dont have acess to perform batch operations on that table icon_sad.gif....
i can only run my query in SPUFI or QMF .. no where else is possible
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat May 02, 2009 4:48 am
Reply with quote

Hello,

You have a management/security issue.

Suggest you explain your requirement to your manager and arrange to be granted read permission to that table (i suspect you already have this or you could not run an online query).

What business reason is there to be able to access a table online and not in batch?
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sat May 02, 2009 5:07 am
Reply with quote

dick scherrer wrote:


What business reason is there to be able to access a table online and not in batch?


That's the same thing which makes me wonder ... icon_smile.gif but this is the way things are here ..
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat May 02, 2009 7:14 am
Reply with quote

Hello,

Quote:
but this is the way things are here ..
Unfortunatly, these kinds of "rules" exist other places also. . .

One way to get delimited data is to include the delimiter (as a literal) between each column selected in your query.
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sat May 02, 2009 2:02 pm
Reply with quote

Hi,

Quote:

One way to get delimited data is to include the delimiter (as a literal) between each column selected in your query.


I didn't get this part.. r u saying some solution to the problem ?? could you please come again in diff words ??
Back to top
View user's profile Send private message
sid_aec

New User


Joined: 01 Jul 2008
Posts: 60
Location: Kolkata

PostPosted: Sun May 03, 2009 2:34 am
Reply with quote

It seems, through QMF if you run the command to fetch your rows first(like select * from 'tablename') and then after getting the resulted rows ,run the command EXPORT DATA TO 'dataset name' , it will be extracted to the dataset and you can import it to the table if required.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun May 03, 2009 3:20 am
Reply with quote

Hello,

Quote:
I didn't get this part..
Try something like:
Code:

select col1 '\' col2 '\' col3 from your_tablename where. . .
The output should have a backslash between each column.

When loading, specify the backslash as the delimiter.
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sun May 03, 2009 11:08 am
Reply with quote

sid_aec wrote:
It seems, through QMF if you run the command to fetch your rows first(like select * from 'tablename') and then after getting the resulted rows ,run the command EXPORT DATA TO 'dataset name' , it will be extracted to the dataset and you can import it to the table if required.


Thanks for the reply sid_aec icon_smile.gif

I tried this QMF export and import too.. dont have acess for that also
.. am able to export to a report but not import from QMF report to tables icon_sad.gif

can u think of any other possible way ?? icon_rolleyes.gif
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sun May 03, 2009 11:17 am
Reply with quote

Quote:


Code:

select col1 '\' col2 '\' col3 from your_table_name where. . .
The output should have a backslash between each column.

ok .. i got so with the delimiters .. from a spufi output dataset ....but how to load this ..???

Quote:

When loading, specify the backslash as the delimiter.

i mean how to do this part ...?? could you please elaborate or provide any pointers to do this(if silly icon_redface.gif) ??
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun May 03, 2009 11:31 am
Reply with quote

Hello,

It sounds like you may only have read access to the table. Suggest you confirm with the dba or the security people before trying any more ways to load data. Have you successfully added rows to this table?

Quote:
(if silly icon_redface.gif ) ??
The only thing that i might consider silly is the particular rule(s) you have to follow. . . icon_confused.gif It may be worth the effort to re-visit the rules and have your manager make the case that if you have read and update access to the table online, you should have the same access in batch.

How many rows are to be loaded? If you have or are granted insert permission, you might generate INSERTs instead of a delimited file for loading.
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sun May 03, 2009 12:42 pm
Reply with quote

Quote:


It sounds like you may only have read access to the table.

No Friend ... i have read acesss to the table which am trying to unload (but only online ) and read as well as update acess to the table to which i
wanna load.. (both via online as well as batch)
Quote:

Have you successfully added rows to this table?.

yes ..

Quote:
The only thing that i might consider silly is the particular rule(s) you have to follow. . . icon_confused.gif It may be worth the effort to re-visit the rules and have your manager make the case that if you have read and update access to the table online, you should have the same access in batch.


BTW .. am trying that tooo ...BUT That process looks like lenghty and complex... these humans are not as fast as our machines icon_wink.gif

Quote:

How many rows are to be loaded? If you have or are granted insert permission, you might generate INSERTs instead of a delimited file for loading.

The count is actually less i can do via insertion itself as of now .. but i want to make my job more easier icon_smile.gif and effective and also i dont want the record count to scare me in future ...that's y i wanna look out any other possible way. am not sure whether it is my 7th or 8th sense this keeps saying me there shud be an alternative way icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun May 03, 2009 1:57 pm
Reply with quote

Hello,

If you have batch access to load data into the "new" table, create the delimited data in the online spufi and write it to a file.

Then, in batch, load the delimited data into the new table.

Possibly i am missing something. . .?

Quote:
these humans are not as fast as our machines
"No" is sometimes given quite quickly. Real bureaucrats even take a long timie to say "no". . . icon_wink.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Sun May 03, 2009 3:51 pm
Reply with quote

Whenever I use "EXPORT/IMPORT" commands in QMF to extract/upload data they work just fine.

When you say:
Quote:
I tried this QMF export and import too..
then this
Quote:
am able to export to a report but not import from QMF report to tables
and then this
Quote:
. . . read as well as update acess to the table to which i
wanna load.. (both via online as well as batch)
these statements tend to contradict each other . . . icon_neutral.gif.

I echo Dick, Possibly I'm missing something. . .?
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sun May 03, 2009 6:22 pm
Reply with quote

oh.. am sorry that am making things complex here ...
let me put it simple icon_smile.gif

Mission:
Extract the Query result in the DCLGEN format.

Acess levels :

To the table which i wanna unload :
ONLY READ via QMF,SPUFI

To the table to which i wanna Upload :

Read as well as update in both online and batch mode .. i mean ... can write a JCL to update/ read or i can run query using SPUFI or QMF.

QMF acess data:

Only EXTRACT REPORT As 'DSN' command is working for both the unload and upload table

The Import report from 'DSN' is not working for both unload and upload table . the operator has denied uploading the QMF report

Sample Data:

Sample Table Structure
Code:

COLUMN NAME        TYPE(LEN
------------------ --------
MONTH              CHAR(10)
VALUE              SMALLINT


Extract Report file is expected in the format

Code:

01  DCLMYFIRST.                                     
    10 MONTH                PIC X(10).             
    10 VALUE                PIC S9(4) USAGE COMP.   


Hope am pretty clear this time ..
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon May 04, 2009 12:13 am
Reply with quote

Hello,

Quote:
Hope am pretty clear this time ..
Unfortunately, not. . .

Quote:
the operator has denied uploading the QMF report
How does an operator "deny" an upload? Why is there an upload at all if all of the work is done on the mainframe?

If you can create a delimited file using spufi on the mainframe and you can load data into the target table on the mainframe, you should be able to do what you want. . .

Quote:
Extract Report file is expected in the format
Why - all you need is a simple delimited file which you said you were abla to create earlier. . .

Suggest it may be time to speak with your dba or other local database support as they can see exactly what you have and can show you how to load the data.
Back to top
View user's profile Send private message
karthik_sripal

New User


Joined: 28 Mar 2008
Posts: 69
Location: 125.16.180.5

PostPosted: Sat May 09, 2009 2:45 pm
Reply with quote

Thanks to all participated in this Discussion .. have spoke with higher heads .. got the acess... icon_smile.gif Solved the problem.icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sat May 09, 2009 9:51 pm
Reply with quote

Good to hear it is working - thank you for letting us know icon_smile.gif

d
Back to top
View user's profile Send private message
skgupta81

New User


Joined: 11 Nov 2008
Posts: 27
Location: Canada

PostPosted: Thu Aug 26, 2010 2:12 am
Reply with quote

While importing via QMF, would it hold the whole table during the IMPORT process...? if yes, Can we ensure that table remains unlocked while such operation by specifying some additional commands?

Anuj Dhawan wrote:
Whenever I use "EXPORT/IMPORT" commands in QMF to extract/upload data they work just fine.

When you say:
Quote:
I tried this QMF export and import too..
then this
Quote:
am able to export to a report but not import from QMF report to tables
and then this
Quote:
. . . read as well as update acess to the table to which i
wanna load.. (both via online as well as batch)
these statements tend to contradict each other . . . icon_neutral.gif.

I echo Dick, Possibly I'm missing something. . .?
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 DELETE SPUFI DB2 1
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
No new posts Adding QMF and SPUFI to the ISPF menu DB2 20
No new posts Need help on formatting a report DFSORT/ICETOOL 14
No new posts Creating Report using SORT DFSORT/ICETOOL 7
Search our Forums:

Back to Top