View previous topic :: View next topic |
Author |
Message |
Anta Beginner
Joined: 10 Oct 2006 Posts: 12 Topics: 5
|
Posted: Fri Apr 04, 2008 6:18 am Post subject: Get QMF procs and queries a usual file |
|
|
Dear friends;
can you please help me with one simple problem? It is not simple for me, to my regret.
There are a lot of QMF procs and queries in some data base.
Does some "one-step" way exist to copy all them into some mvs file?
Thans in advance. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Fri Apr 04, 2008 11:35 am Post subject: |
|
|
you can generate an export command for every query query you have and store it as a proc and run that proc which actually does the export.QMF stores information about stored objects in Q.QUERY_LIST.
Use the following query to generate the export commands.
Code: |
SELECT CHAR('EXPORT QUERY ')
,CHAR(OWNER,8) ||
CHAR('.') ||
CHAR(NAME,8)
,CHAR( ' TO ')
,CHAR( '''')
,CHAR('tid.QMF.QUERIES(')
,CHAR(NAME,8)
,CHAR(')')
,CHAR('''')
FROM Q.QUERY_LIST
WHERE OWNER = 'your id'
;
|
once you have generated the export commands you just run it in QMF. You can do the same for the procs also
Hope this helps...
Cheers _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
Anta Beginner
Joined: 10 Oct 2006 Posts: 12 Topics: 5
|
Posted: Sun Apr 06, 2008 11:41 pm Post subject: |
|
|
Kolusu,
thank you a lot!
I have completely forgotten about the existence of the Q-tbl which contains the list of QMF-queries.
With thanks,
Anta |
|
Back to top |
|
|
andrea Beginner
Joined: 20 Apr 2016 Posts: 3 Topics: 0
|
Posted: Thu Apr 21, 2016 3:11 am Post subject: |
|
|
I have the same problem, but I start from DB2 v6.1, without
Q.QUERY_LIST.
Is there any way to save thenth of QMF objects without reformatting them because of how data are stored in OBJECT_DATA?
Thanks in advance,
Andrea |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Thu Apr 21, 2016 9:32 am Post subject: |
|
|
andrea wrote: | I have the same problem, but I start from DB2 v6.1, without
Q.QUERY_LIST.
Is there any way to save thenth of QMF objects without reformatting them because of how data are stored in OBJECT_DATA?
Thanks in advance,
Andrea |
Andrea,
You mean you are running QMF 6.1? either way that is real an old version. The QMF objects are all stored in the tables namely Q.OBJECT_DIRECTORY, Q.OBJECT_DATA ...
See if you can run this query
Code: |
Code
SELECT *
FROM Q.OBJECT_DIRECTORY WHERE OWNER = 'your T-id'
or
SELECT *
FROM Q.OBJECT_DATA WHERE OWNER = 'your T-id'
|
If you have data in any of the above queries then you can download that data in raw format. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
andrea Beginner
Joined: 20 Apr 2016 Posts: 3 Topics: 0
|
Posted: Thu Apr 21, 2016 10:34 am Post subject: |
|
|
Hi kolusu, thanks for your reply.
I meant DB2 version 6.1, but I just checked, and QMF version is 6.1 as well.
If I run the query
SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR = 'Q' ORDER BY TYPE, NAME, I obtain:
Code: | NAME CREATOR TYPE
------------------ -------- ----
COMMAND_SYNONYMS Q T
DSQ_RESERVED Q T
ERROR_LOG Q T
OBJECT_DATA Q T
OBJECT_DIRECTORY Q T
OBJECT_REMARKS Q T
PROFILES Q T
RESOURCE_TABLE Q T
DSQEC_ALIASES Q V
DSQEC_COLS_LDB2 Q V
DSQEC_COLS_RDB2 Q V
DSQEC_QMFOBJS Q V
DSQEC_TABS_LDB2 Q V
DSQEC_TABS_RDB2 Q V
RESOURCE_VIEW Q V
|
I know original "Q" tables anb views, but I would have liked to get data (i.e. original queries, forms and procs) formatted in the same way I see when I enter for example DIS queryname without reformatting them using a text editor.
And, mainly, I need to save tenth of objects in one step only, without exporting them one by one. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Thu Apr 21, 2016 11:13 am Post subject: |
|
|
andrea,
Did you try to use the query shown in post 2 but use Q.OBJECT_DATA table instead of Q.QUERY_LIST and see if you can export the queries?
The column APPLDATA in the table Q.OBJECT data has your query and the column SEQ shows the line number in the SQL query. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
andrea Beginner
Joined: 20 Apr 2016 Posts: 3 Topics: 0
|
Posted: Tue Apr 26, 2016 3:23 am Post subject: |
|
|
kolusu wrote: | andrea,
Did you try to use the query shown in post 2 but use Q.OBJECT_DATA table instead of Q.QUERY_LIST and see if you can export the queries?
The column APPLDATA in the table Q.OBJECT data has your query and the column SEQ shows the line number in the SQL query. |
Hi kolusu, thanks a lot for the suggestion.
I made some little change to your query just to format it.
It runs succesfully in this way:
Code: |
SELECT CHAR('EXPORT QUERY ')
,STRIP(CHAR(OWNER , 8)) !!
CHAR('.') !!
CHAR(NAME , 8)
,CHAR( ' TO ')
,CHAR( '''') !!
STRIP( CHAR('C60482.QMF.QUERIES(') !!
CHAR(NAME , 8) ) !!
CHAR(')') !!
CHAR('''')
FROM Q.OBJECT_DATA
WHERE OWNER = 'id_I_need'
;
|
I only need a way to export queries with long name or with underscores in their name, but the most of the work is done.
Great !! |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12359 Topics: 75 Location: San Jose
|
Posted: Tue Apr 26, 2016 10:40 am Post subject: |
|
|
andrea wrote: |
I only need a way to export queries with long name or with underscores in their name, but the most of the work is done. |
Andrea,
It is quite simple to get the long name queries and also queries with underscore in them. You also need to rememebr that you might generate duplicate export queries depending on the seq. You also need to watch out for the truncation warning.
use the following query.
Code: |
SELECT CHAR('EXPORT QUERY ')
,STRIP(CHAR(OWNER , 8)) ||
CHAR('.') ||
STRIP(CHAR(NAME,12))
,CHAR( ' TO ')
,CHAR( '''') ||
STRIP(CHAR('C60482.QMF.QUERIES(') ||
CASE WHEN LENGTH(NAME) > 8
OR NAME LIKE '%_%'
THEN CHAR(REPLACE(SUBSTR(NAME,1,9),'_',''))
ELSE CHAR(NAME , 8)
END) ||
CHAR(')') ||
CHAR('''')
FROM Q.OBJECT_DATA
WHERE OWNER = 'id_I_need'
AND SEQ = 1
AND (LENGTH(NAME) > 8
OR NAME LIKE '%_%')
;
|
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
Back to top |
|
|
|
|