Wednesday, May 13, 2020

Discovering the database's limits

using sql to see the maximum sizes of the ibm i database

Every so often I stumble across something in Db2 for i that I may not have a practical use for, but I find interesting. The SQL table SQL_SIZING is a good example of this. It contains one row for each limit of the IBM i Db2 database manager.

The table contains just four columns:

  1. SIZING_ID:  The ANSI, and ISO, number that defines the database sizing id
  2. SIZING_NAME:  The ANSI, and ISO, database sizing id name
  3. SUPPORTED_VALUE:  Sizing limit (value). If null the sizing limit is not applicable for IBM i
  4. COMMENTS:  Even though the sizing name is big enough to contain an adequate description of the sizing id this column gives a more detailed description

The syntax to view this information is just the following simple SQL statement:

SELECT * FROM QSYS2.SQL_SIZING
         ORDER BY SIZING_ID

The results are hard to show on this page due to the width of the SIZING_NAME column, therefore, I am going to curtail the width of the column to fit in the width of this page, and not show the COMMENTS column:

SIZING                               SUPPORTED
_ID    SIZING_NAME                   _VALUE
------ ----------------------------- ---------
     0 MAXIMUM DRIVER CONNECTIONS            0 
     1 MAXIMUM CONCURRENT ACTIVITIES         0
    30 MAXIMUM COLUMN NAME LENGTH          128
    31 MAXIMUM CURSOR NAME LENGTH          128
    32 MAXIMUM SCHEMA NAME LENGTH          128

The values that caught my eye were:

SIZING                                               SUPPORTED
_ID    SIZING_NAME                                      _VALUE
------ ---------------------------------------- --------------
    30 MAXIMUM COLUMN NAME LENGTH                          128
    31 MAXIMUM CURSOR NAME LENGTH                          128
    32 MAXIMUM SCHEMA NAME LENGTH                          128
    35 MAXIMUM TABLE NAME LENGTH                           128
   100 MAXIMUM COLUMNS IN SELECT                          8000
   101 MAXIMUM COLUMNS IN TABLE                           8000
   106 MAXIMUM TABLES IN SELECT                           1000
 15101 MAXIMUM ROW LENGTH                                32766
 15300 MAXIMUM NUMBER OF TRIGGERS*                         300
 15500 MAXIMUM KEY COLUMNS                                 120
 15501 MAXIMUM KEY LENGTH                                32767
 16100 MAXIMUM NUMBER OF MEMBERS                         32767
 16101 MAXIMUM NUMBER OF RECORD FORMATS                     32
 18401 MAXIMUM NUMBER OF OBJECTS IN A LIBRARY          1000000
 18409 MAXIMUM NUMBER OF BYTES IN A STREAM FILE  1099511627776
 19000 MAXIMUM NUMBER OF JOBS                           970000
 19001 MAXIMUM NUMBER OF SPOOLED FILES PER JOB          999999

* On each table

My first thought was has this changed between the latest releases of IBM i?

Thanks to RZKH I can use three IBM i partitions with the following releases:

IBM i
release
Partition
name
7.2 DEV720
7.3 DEV730
7.4 DEV740

As I am on DEV740 I can get data from the other two partitions using the "three part name" SQL name for the table:

SELECT COUNT(*) FROM DEV720.QSYS2.SQL_SIZING ;
SELECT COUNT(*) FROM DEV730.QSYS2.SQL_SIZING ;
SELECT COUNT(*) FROM QSYS2.SQL_SIZING ;

The results are as follows:

IBM i
release
No. of
rows
7.2 105
7.3 106
7.4 106

What is the difference between IBM i 7.2 and 7.4?

The these two SQL statement give me what I need.

01  CREATE TABLE QTEMP.DEV720 (DEV720_ID,DEV720_VALUE,DEV720_NAME)
02  AS (SELECT SIZING_ID,SUPPORTED_VALUE,SIZING_NAME 
03        FROM DEV720.QSYS2.SQL_SIZING) WITH DATA ;

04  SELECT A.SIZING_ID,A.SIZING_NAME,B.DEV720_ID,B.DEV720_NAME
05    FROM QSYS2.SQL_SIZING A FULL OUTER JOIN
06         QTEMP.DEV720 B
07      ON A.SIZING_ID = B.DEV720_ID 
08   WHERE A.SIZING_ID IS NULL
09      OR B.DEV720_ID IS NULL

Lines 1 – 3: As I cannot join remote and local tables first I need to create a table that contains the results from DEV720.

Lines 4 – 9: When I join the two tables with a FULL OUTER JOIN, line 5, the result of the join includes all the rows from the two tables, even the unmatched ones. To only return the unmatched rows I need to check either of the SIZING_ID columns for null, lines 8 and 9.

I have reformatted the results to fit on this page...

SIZING
_ID    SIZING_NAME
------ --------------------------------------------
7.4 and not in 7.2
 15403 MAXIMUM EVI INDEX SIZE
 18600 MAXIMUM NUMBER OF ENTRIES FOR A USER PROFILE

7.2 and not in 7.4
 16806 MAXIMUM EXTENDED DYNAMIC PACKAGE SIZE

Even though IBM i 7.3 and 7.4 have the same number of rows are there any differences?

To check I just modify SQL statement I used before:

01  CREATE TABLE QTEMP.DEV730 (DEV730_ID,DEV730_VALUE,DEV730_NAME)
02  AS (SELECT SIZING_ID,SUPPORTED_VALUE,SIZING_NAME 
03        FROM DEV730.QSYS2.SQL_SIZING) WITH DATA ;

04  SELECT A.SIZING_ID,A.SIZING_NAME,B.DEV730_ID,B.DEV730_NAME
05    FROM QSYS2.SQL_SIZING A FULL OUTER JOIN
06         QTEMP.DEV730 B
07      ON A.SIZING_ID = B.DEV730_ID 
08   WHERE A.SIZING_ID IS NULL
09      OR B.DEV730_ID IS NULL

I am glad I checked as there is a difference:

SIZING
_ID    SIZING_NAME
------ --------------------------------------------
7.4 and not in 7.3
 18600 MAXIMUM NUMBER OF ENTRIES FOR A USER PROFILE

7.3 and not in 7.4
 16806 MAXIMUM EXTENDED DYNAMIC PACKAGE SIZE

I can build a SQL statement to compare the SUPPORTED_VALUES to see if the values in DEV730 and DEV720 are any different from those in DEV740

01  SELECT A.SIZING_ID,
02         B.DEV720_VALUE AS "7.2",
03         C.DEV730_VALUE AS "7.3",
04         SUPPORTED_VALUE AS "7.4"
05  FROM QSYS2.SQL_SIZING A 
06       LEFT OUTER JOIN QTEMP.DEV720 B
07       ON A.SIZING_ID = B.DEV720_ID 
08       LEFT OUTER JOIN QTEMP.DEV730 C
09       ON A.SIZING_ID = C.DEV730_ID
10  ORDER BY SIZING_ID

By using the LEFT OUTER JOIN, lines 6 and 8, I am only including the results from the DEV720 and DEV730 tables when they match the rows from DEV740, as I am not concerned with the rows that are in DEV720 and DEV730 and not in DEV740 table.

I am not going to show the results as the values for all three partitions were identical.

You should run the simplest SQL statement and find what database limits you need to operate within.

SELECT * FROM QSYS2.SQL_SIZING
         ORDER BY SIZING_ID

 

You can learn more about the SQL_SIZING table from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.