View previous topic :: View next topic
|
Author |
Message |
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
why don't you use the sql with UNLOAD to extract from your database?
then everything would be 'loadable'. |
|
Back to top |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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 ....
i can only run my query in SPUFI or QMF .. no where else is possible |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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 ... but this is the way things are here .. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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 |
|
|
sid_aec
New User
Joined: 01 Jul 2008 Posts: 60 Location: Kolkata
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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
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
can u think of any other possible way ?? |
|
Back to top |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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 ) ?? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 ) ?? |
The only thing that i might consider silly is the particular rule(s) you have to follow. . . 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 |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
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. . . 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
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 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 |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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". . . |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 . . . .
I echo Dick, Possibly I'm missing something. . .? |
|
Back to top |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
oh.. am sorry that am making things complex here ...
let me put it simple
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
karthik_sripal
New User
Joined: 28 Mar 2008 Posts: 69 Location: 125.16.180.5
|
|
|
|
Thanks to all participated in this Discussion .. have spoke with higher heads .. got the acess... Solved the problem. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Good to hear it is working - thank you for letting us know
d |
|
Back to top |
|
|
skgupta81
New User
Joined: 11 Nov 2008 Posts: 27 Location: Canada
|
|
|
|
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 . . . .
I echo Dick, Possibly I'm missing something. . .? |
|
|
Back to top |
|
|
|