Programming Languages on Power

Power Programming Languages

IBM Power, including the AIX, IBM i, and Linux operating systems, support a wide range of programming languages, catering to both traditional enterprise applications and modern development needs.


#Power

 View Only
Back to discussions
Expand all | Collapse all

find tables and all related objects (even in other libraries) and delete them

  • 1.  find tables and all related objects (even in other libraries) and delete them

    Posted Fri February 24, 2023 03:29 PM

    We have a library (AALib) that is based on a third party connection. We're moving all objects from that library to our standard production library (ProdLib). Some objects in AALib, like tables for instance, have related objects in other libraries. I need to write a query that reports all objects in AALib and all related objects. Second requirement is to delete all objects and recreate them in our standard change management system. I think this an opportunity to use qCmdExc as a scalar function. Seems like I saw an example that achieves my goal about a year ago; maybe a Scott Forstie video or something. Does anyone have an example or can point me to the example I think I saw? 
    Thank you. I'm just looking for a jumpstart.



    ------------------------------
    Patrick Conner
    ------------------------------

    #SQL


  • 2.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Fri February 24, 2023 08:42 PM
    Edited by Satid Singkorapoom Fri February 24, 2023 09:39 PM

    Dear Patrick

    Your first question reminds me of an old DB2 UDB for OS/400 GUI tool in the old IBM i Navigator for Windows named Database Navigator (DBNav) that was new aroud the time of OS/400 V5R1 and it looks like this :  https://www.mcpressonline.com/it-infrastructure/system-administration/get-a-clear-picture-of-your-database

    Get a Clear Picture of Your Database | System Administration | IT Infrastructure
    I never used it from the browser-based Navigator for i and so have no idea if it is still there or not but you can try it just to help you get an informative graphical map of the relationship among the DB objects you need to work on. 

    The answer to your first question is to do a metadata query to relevant DB2 catalog on view, index, foreign key constraint, referential constraint (and trigger?)    Kent may be able to provide an efficient way to do this.    Scott Forstie (and Tim Rowe) has many teaching videos in COMMON web site.   

    This blog post may be useful for you as well : https://www.rpgpgm.com/2019/10/getting-database-relationships-using-sql.html



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: find tables and all related objects (even in other libraries) and delete them
    Best Answer

    Posted Sat February 25, 2023 04:15 AM

    There is a User Defined Table Function (UDTF) in the SYSTOOLS Schema which will return all related Object for a given table:

    Select *
      from Table(SysTools.Related_Objects(Library_Name => 'COMSQLQRY', 
                                          File_Name => 'ORDERHDRX'))

    You may read all your tables from SysTables and then call this UDTF for each of them.



    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------

    Original Message


  • 4.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Sun February 26, 2023 03:04 AM
    Edited by Satid Singkorapoom Sun February 26, 2023 03:05 AM

    More discussion on RELATED_OBJECTS table function :  https://www.rpgpgm.com/2020/12/new-sql-table-function-lists-all.html



    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 5.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Mon February 27, 2023 08:54 AM
    Edited by Patrick Conner Mon February 27, 2023 03:02 PM

    I ran into a problem. I'm trying to run:

    select s.*
    from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => 'SQAATYPE9', OBJTYPELIST => 'ALL')) s
    union
    select s2.*
    from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => 'SQAATYPE9', OBJTYPELIST => 'ALL')) s, 
    lateral (select * from table( sysTools.RELATED_OBJECTS(LIBRARY_NAME => s.objLib, FILE_NAME => s.objName))) r,
    lateral ( select * from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => r.library_name, object_name => r.system_name, OBJTYPELIST => 'ALL'))) s2

    but I get error: 

     SQL State: 57005
    Vendor Code: -666
    Message: [SQL0666] SQL query exceeds specified limit or threshold. Cause . . . . . :   A database query was ended by query monitoring tool 1. The tools are:  1 -- Predictive Query Governor

    I'm using ACS Run SQL Scripts by the way.

    When I break the two queries apart they run fine.

    The first query retrieves 97 rows and runs in milliseconds.
    select s.* from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => 'SQAATYPE9', OBJTYPELIST => 'ALL')) s
    Statement ran successfully   (180 ms)

    The second query initially retrieves 100 rows, but when I pagedown it stops at 126
    select s2.* from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => 'SQAATYPE9', OBJTYPELIST => 'ALL')) s, lateral (select * from table( sysTools.RELATED_OBJECTS(LIBRARY_NAME => s.objLib, FILE_NAME => s.objName))) r, lateral ( select * from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => r.library_name, object_name => r.system_name, OBJTYPELIST => 'ALL'))) s2
    Statement ran successfully   (1,330 ms  =  1.330 sec)

    The Predictive Query Governor is guessing 927 seconds of run time which exceeds 300. Seems like the guess is a little excessive. Anything I can do to influence the guess?

    Predictive Query Governor: A database query was about to be started whose estimated run time of 927 exceeds the specified limit of 300 or whose estimated temporary storage usage of 28 exceeds the specified limit of 2147352576.
    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 6.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Tue February 28, 2023 02:11 AM
    Edited by Satid Singkorapoom Tue February 28, 2023 06:48 AM

    Dear Patrick

    One thing to reduce the estimated and actual total processing time is to select only as few columns as you need rather than *. 

    https://www.ibm.com/docs/en/i/7.3?topic=services-object-statistics-table-function
    https://www.rpgpgm.com/2016/01/using-sql-for-objects-statistics.html

    One more thing you can do is to enable parallel degree (SMP) for your statement. Assuming you have DB2 SMP installed and licensed key entered, you precede your statement with SET CURRENT DEGREE = 'N';  Depending on how many CPU cores you have in your LPAR, you may start with value N of 3 or 4. If this still results in estimated run-time more than 300, increase the degree.  Please also select as few columns as needed.   Once you have no need for parallel degree, set N to 1.

    If we know the physical file name(s) the table function use, we can create index(es) over the columns we want to retrieve from but this option can be tough as there is no such information available. Index creation time can also be quite long as this kind of tables can contain a huge amount of data.

    As of IBM i 7.3 (at a certain TR level), there is a new DB2 Query Supervisor providing you with an ability to set run-time and/or resource consumption limits that invokes an exit program that you create to handle the case rather refusal to run as the Query Governor has been doing.  For example, you may write an exit program to ask for a password within a certain time period that would allow the query to continue to run :-) 

    https://www.ibm.com/support/pages/query-supervisor  

    https://www.itjungle.com/2021/04/14/query-supervisor-gives-database-engineers-new-power/


    ------------------------------
    Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 7.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Wed March 01, 2023 10:06 AM

    Optimizer's estimates are a relative measurement, not really meant to be the exact amount of time the query takes.  That's why normally we don't recommend clients set such a low time limit like 300  seconds.  As Satid points out, the Query Supervisor was delivered so clients could set limits based on the actual runtime.

    Maybe you use the OVERRIDE_QAQQINI service in your SQL Scripts session to set a higher time limit like 10 or 15 mins.



    ------------------------------
    Kent Milligan
    ------------------------------

    Original Message


  • 8.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Fri April 07, 2023 09:17 PM

    Would including the object type in the second lateral help reduce the resultset?
    select * from table( qSys2.OBJECT_STATISTICS(OBJECT_SCHEMA => r.library_name, object_name => r.system_name, OBJTYPELIST => R.OBJTYPE))) s2



    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 9.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Wed April 26, 2023 04:30 PM

    r.ObjType is not available to me. Probably an op sys/TR thing.



    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


  • 10.  RE: find tables and all related objects (even in other libraries) and delete them

    Posted Wed April 26, 2023 04:35 PM

    Update: On our dev system the value of qQryTimLmt was up'd to 600 and the query runs. 



    ------------------------------
    Patrick Conner
    ------------------------------

    Original Message


Related Content