PK62178: NEW FUNCTION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• New Function
  
  DB2MIGV9/K
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 V9 users of implicit databases.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: The maximum number of databases that    *
  *                      can be created implicitly is hard-coded *
  *                      to 60000.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The maximum number of databases that can be created implicitly
  is hard-coded to 60000.  For some users, this number is too high
  and does not leave enough DBIDs available for explicitly created
  databases.
  

Problem conclusion

Temporary fix

Comments

• The name generated for implicit databases follows the naming
  convention of DSNxxxxx, where xxxxx is a number ranging from
  00001 to 60000 and is controlled internally by the system
  sequence object SYSIBM.DSNSEQ_IMPLICITDB.
  
  For new users installing or migrating to V9, the default maximum
  number of databases that can be created implicitly will be
  lowered from 60000 to 10000.
  
  For existing users who wish to lower the 60000 limit, the
  maximum value for the sequence SYSIBM.DSNSEQ_IMPLICITDB can
  be altered by IDs with the installation SYSADM authority using
  the ALTER SEQUENCE statement with the MAXVALUE option.  If the
  new maximum is lower than the number of existing implicit
  databases, DB2 will try to create or reuse database DSN00001 the
  next time an implicit database is needed and continue up to the
  new maximum.  Implicit databases beyond the new maximum will not
  be used.
  
  CHANGES TO DOCUMENTATION:
  
  Introduction to DB2 for z/OS
  Creation of databases
  In new-function mode, if you do not specify the IN clause on the
  CREATE TABLE statement, DB2 implicitly creates a database.  The
  following list shows the names for an implicit database when the
  maximum value of the sequence SYSIBM.DSNSEQ_IMPLICITDB is 10000:
  
  DSN00001, DSN00002, DSN00003, ..., DSN09999, and DSN10000
  
  Administration Guide
  Implementing your database design
  Tip: You can simplify your database implementation by letting
  DB2 implicitly create certain objects for you.  On a CREATE
  TABLE statement, if you do not specify a database name, DB2 will
  use an existing implicitly created database.  If an implicitly
  created database does not exist, DB2 will create one using the
  naming convention of DSNxxxxx (from DSN00001 to DSNnnnnn, where
  nnnnn is the maximum value of the sequence
  SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000) for the user.
  
  SQL Reference
  CREATE TABLE
  If the IN clause is not specified: If you do not specify the IN
  clause, DB2 will implicitly create a table space as described
  previously, but DB2 will also choose a database as follows:
  
  - DB2 chooses a name of the form DSNnnnnn, where nnnnn is
    between 00001 and the maximum value of the sequence
    SYSIBM.DSNSEQ_IMPLICITDB, with a default of 10000, inclusive.
    - If DSNnnnnn already exists and is an implicitly created
      database, DB2 creates the table in that database.
    - If DSNnnnnn does not exist, DB2 creates a database with the
      name DSNnnnnn.
  
      If DSNnnnnn cannot be created because of a deadlock,
      timeout, or resource unavailable condition, DB2 increments
      nnnnn by one and tries the resultant database name.
      Assuming the maximum value of the sequence
      SYSIBM.DSNSEQ_IMPLICITDB is 10000, if DB2 reaches 10000,
      and DSN10000 is not available, DB2 sets nnnnn to 00001 and
      tries the resultant database name.  If DB2 tries n database
      names without success, where n is the maximum value of the
      sequence SYSIBM.DSNSEQ_IMPLICITDB, an error occurs.
  
  Statements allowed on the catalog
  Certain SQL statements can be used to change the value of
  certain options for existing catalog indexes, sequences, and
  table spaces, and to add indexes to any of the catalog tables.
  Table "SQL statements that can be used to change existing
  catalog indexes, sequences, and table spaces, and to add indexes
  to any of the catalog tables"
  +----------------+-------+-------------------------------------+
  | SQL statement  | Index | Allowable clauses and usage notes   |
  +----------------+-------+-------------------------------------+
  | ALTER SEQUENCE |       | The only clause allowed is          |
  |                |       | MAXVALUE.                           |
  |                |       |                                     |
  |                |       | You can only alter the MAXVALUE     |
  |                |       | value of the catalog sequence       |
  |                |       | DSNSEQ_IMPLICITDB, and the value    |
  |                |       | specified must be an integer        |
  |                |       | between 1 and 60000, inclusive.     |
  +----------------+-------+-------------------------------------+
  
  Utility Guide and Reference/SQL Reference
  Limits in DB2 for z/OS, Table DB2 system limits
  +------------------------------+-------------------------------+
  | Item                         | Limit                         |
  +------------------------------+-------------------------------+
  | Maximum number of implicitly | Maximum value of the sequence |
  | created databases            | SYSIBM.DSNSEQ_IMPLICITDB,     |
  |                              | with a default of 10000       |
  +--------------------------------------------------------------+
  
  ADDITIONAL KEYWORDS:
  SQLALTER
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UK44489

Modules/Macros

• DSNTISB5 DSNUECSE DSNXIAQ1 DSNXIASQ
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK44489

     UP09/03/18 P F903

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.