Discovering the database's limits

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.