MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Get QMF procs and queries a usual file

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
Anta
Beginner


Joined: 10 Oct 2006
Posts: 12
Topics: 5

PostPosted: Fri Apr 04, 2008 6:18 am    Post subject: Get QMF procs and queries a usual file Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Fri Apr 04, 2008 11:35 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Anta
Beginner


Joined: 10 Oct 2006
Posts: 12
Topics: 5

PostPosted: Sun Apr 06, 2008 11:41 pm    Post subject: Reply with quote

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
View user's profile Send private message
andrea
Beginner


Joined: 20 Apr 2016
Posts: 3
Topics: 0

PostPosted: Thu Apr 21, 2016 3:11 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 21, 2016 9:32 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
andrea
Beginner


Joined: 20 Apr 2016
Posts: 3
Topics: 0

PostPosted: Thu Apr 21, 2016 10:34 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Thu Apr 21, 2016 11:13 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
andrea
Beginner


Joined: 20 Apr 2016
Posts: 3
Topics: 0

PostPosted: Tue Apr 26, 2016 3:23 am    Post subject: Reply with quote

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 !! Very Happy
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12357
Topics: 75
Location: San Jose

PostPosted: Tue Apr 26, 2016 10:40 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group