Db2 for z/OS & Db2ZAI

Db2 for z/OS and its ecosystem

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Questions on a specific DB2 command

    Posted Thu April 10, 2025 03:02 PM

    Hi all,

    our customer has more than 5000 databases.

    With our command "Display DB(*) ONLY LIMIT(*)" ,  the amount of Database returned is going to be limited by the space so we need an alternative to have a complete listing of all the Databases.

    Options are:

    1. Try out the command with different LIMIT values to confirm this is what is happening. 
    2. Try out command with SPACENAM(*) on their system to see if this completes ok.
    3. Try out command with SPACENAM filters specified in order to break up the query. 

    also:

    1. What exactly is the "space" that's limiting the outputs from the command?     
    2. Is that space something that the customer can "increase", and what's the implication to the customer if they increase the space?
    3. If increasing the space is an appropriate action, how can that space be increased (specific command to use)?

    Thanks a lot for your help!



    ------------------------------
    Lucia Santucci
    zAnalytics Support Team Lead
    ------------------------------


  • 2.  RE: Questions on a specific DB2 command

    Posted Fri April 11, 2025 01:48 AM

    Hello Lucia,

    I suppose that the storage for the messages is restricted by the Db2-Command-Processor (DSN), I don't know how this can be increased.

    But it is possible to execute Db2-Commands by IFI-Calls (DSNWLI2, COMMAND). Once ago there was a good IDUG presentation on the web ('The Joy of DB2') with a good example, but it seems to be gone.

    DSNWLI-calls can be done by programs or even by REXX, and here it is possible to control the size of the Return-Area. But of course, it needs some coding.

    If you're interested in this point please let me know, I will look for an example.

    Kind regards

    Rolf



    ------------------------------
    Rolf Drees
    ------------------------------

    Original Message


  • 3.  RE: Questions on a specific DB2 command

    Posted Fri April 11, 2025 03:51 AM

    SELECT 'Display DB(' || STRIP(NAME) ||') ONLY LIMIT(*)'
    FROM SYSIBM.SYSDATABASE;

    I have a little REXX that takes the output from DSNTIAUL and reformats so it can be fed back into the command processor (or DSNTEP2).  Easy enough to do.



    ------------------------------
    James Campbell
    ------------------------------

    Original Message


  • 4.  RE: Questions on a specific DB2 command

    Posted Fri April 11, 2025 09:02 AM

    Lucia -

    Try running the command in a batch TSO job.  Should work just fine.

    Mike



    ------------------------------
    Mike Brauweiler
    Consulting Systems Engineer
    Mainline Information Systems

    ------------------------------

    Original Message


  • 5.  RE: Questions on a specific DB2 command

    Posted Fri April 11, 2025 11:29 AM

    Lucia,

    What exactly do you mean by "a complete of all the databases".

    • If you simply want the database names then you should query SYSIBM.SYSDATABASE
    • If you want the full DDL for all your databases (perhaps to put into source control) then you will need to use an appropriate tool from either IBM or one of the third party vendors to achieve that
    • If you are wanting to look at the status of the databases, and potentially the tablespaces within each database, then the DISPLAY command is what you want.

    In the last case adding SPACENAM(*) increases massively the amount of data returned, as it then explodes to tablespace level.   Typically people will be running the DISPLAY DB command to look for tablespaces in bad states.  Traditionally you would use the RESTRICT keyword to do that.  You probably would also want to add some of the ADVISORY options to that nowadays as well, as some of those "advisory" states are very much in the "restrict" category.

    HTH

    Phil



    ------------------------------
    Philip Nelson
    Senior Database Software Engineer
    Lloyds Banking Group / ScotDB Limited
    Edinburgh
    ------------------------------

    Original Message


  • 6.  RE: Questions on a specific DB2 command

    Posted Mon April 14, 2025 09:55 AM

    You can always do it by the ABC.

    -DIS DB(A*)SP(*)

    -DIS DB(B*)SP(*)

    ...



    ------------------------------
    Isaac Yassin
    ------------------------------

    Original Message


  • 7.  RE: Questions on a specific DB2 command

    Posted Tue April 22, 2025 04:44 AM
    Edited by Ron Hameiri Tue April 22, 2025 04:47 AM

    Hello Lucia,

    The key to solving this issue is the use of the AFTER keyword in the DISPLAY DATABASE command.

    I encountered this issue when working with over 30,000 objects, which caused Db2 to return the DSNT306I message due to memory contstraints.
    To resolve it, I implemented a logic that processes each database individually. 
    For every database, the following command is executed:
      DISPLAY DATABASE(dbName) SPACENUM(*) LIMIT(*)
    Next, based on the last SPACENAM returned in the output, the following command is executed:
      DISPLAY DATABASE(dbName) SPACENAM(lastSpaceNam) LIMIT(*) AFTER
    The lastSpaceNam variable stores the name of the last SPACENAM from the previous output.
    This allows the process to continue retrieving additional space names without hitting the DSNT306I limit.



    ------------------------------
    Ron Hameiri  
    ------------------------------

    Original Message


Related Content