SQL comments The following rules apply to the use of SQL comments The two hyphens must be on the same line, not separated by a space Comments can be started wherever a space is valid (except within a delimiter token or between ' EXEC and ' SQL). Comments are terminated by the end of the line Comments are not allowed within statements that are dynamically prepared (using PREPaRe or EXECUTE IMMEDIATE) In COBOl, the hyphens must be preceded by a space Example: This example shows how to include comments in an SQL statement within a C program EXEC SQ CREATE VIEW PRU MAXPER project FROM PROJECT WHERE DEPTNO =E21 systems support dept code AND PRSTAFF > 1 Related reference: Select-statement"in the SQL Reference, Volume 1 “ EXECUTE" on page544 OPEN on page 615 "PREPARE"on page 620 SQLCA (SQL communications area)"in the SQL Reference, Volume 1 SQL procedure statement"on page 757 Chapter 1. Statements 11
The following rules apply to the use of SQL comments: v The two hyphens must be on the same line, not separated by a space. v Comments can be started wherever a space is valid (except within a delimiter token or between 'EXEC' and 'SQL'). v Comments are terminated by the end of the line. v Comments are not allowed within statements that are dynamically prepared (using PREPARE or EXECUTE IMMEDIATE). v In COBOL, the hyphens must be preceded by a space. Example: This example shows how to include comments in an SQL statement within a C program: EXEC SQL CREATE VIEW PRJ_MAXPER -- projects with most support personnel AS SELECT PROJNO, PROJNAME -- number and name of project FROM PROJECT WHERE DEPTNO = ’E21’ -- systems support dept code AND PRSTAFF > 1; Related reference: v “Select-statement” in the SQL Reference, Volume 1 v “EXECUTE” on page 544 v “OPEN” on page 615 v “PREPARE” on page 620 v “SQLCA (SQL communications area)” in the SQL Reference, Volume 1 v “SQL procedure statement” on page 757 SQL comments Chapter 1. Statements 11
ALTER BUFFERPOOL ALTER BUFFERPOOL The ALTER BUFFERPOOL statement is used to do the following: modify the size of the buffer pool on all partitions or on a single partition turn on or off the use of extended storage add this buffer pool definition to a new database partition group modify the block area of the buffer pool for block-based I/O Invocation: This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509) Authorization: The authorization id of the statement must have sysctl or SYSaDm authorit PP--ALTER BUFFERPOOL-bufferpool-name- EDIATE SIZE--number-of-pages RTITIONNUM-db-partition-numt TENDED STORAGE. DD DATABASE PARTITION GROUP-db-partition-group LOCKPAGES--number-of-pag LOCKSIZE-number-of-page LOCKSIZE-number-of-pages- Description bu fferpool-name Names the buffer pool. This is a one-part name. It is an SQL identifier (either ordinary or delimited). It must be a buffer pool described in the DBPARTITIONNUM db-partition-number Specifies the partition on which size of the buffer pootns for the buffer oI is modified. The partition must be in one of the database partition grou pool(SQLSTATE 42729). If this clause is not specified, then the size of the buffer pool is modified on all partitions on which the buffer pool exists that used the default size for the buffer pool(did not have a size specified in the except-on-db-partitions-clause of the CREATE BUFFERPOOL statement) [2 SQL Reference, Volume 2
ALTER BUFFERPOOL The ALTER BUFFERPOOL statement is used to do the following: v modify the size of the buffer pool on all partitions or on a single partition v turn on or off the use of extended storage v add this buffer pool definition to a new database partition group v modify the block area of the buffer pool for block-based I/O. Invocation: This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). Authorization: The authorization ID of the statement must have SYSCTRL or SYSADM authority. Syntax: ALTER BUFFERPOOL bufferpool-name IMMEDIATE SIZE number-of-pages DEFERRED DBPARTITIONNUM db-partition-number NOT EXTENDED STORAGE EXTENDED STORAGE ADD DATABASE PARTITION GROUP db-partition-group-name NUMBLOCKPAGES number-of-pages BLOCKSIZE number-of-pages BLOCKSIZE number-of-pages Description: bufferpool-name Names the buffer pool. This is a one-part name. It is an SQL identifier (either ordinary or delimited). It must be a buffer pool described in the catalog. DBPARTITIONNUM db-partition-number Specifies the partition on which size of the buffer pool is modified. The partition must be in one of the database partition groups for the buffer pool (SQLSTATE 42729). If this clause is not specified, then the size of the buffer pool is modified on all partitions on which the buffer pool exists that used the default size for the buffer pool (did not have a size specified in the except-on-db-partitions-clause of the CREATE BUFFERPOOL statement). ALTER BUFFERPOOL 12 SQL Reference, Volume 2
ALTER BUFFERPOOL SIZE num the buffer pool specified as the number of pages IMMEDIATE The bufferpool size will be changed immediately. If there is not enough reserved space in the database shared memory to allocate new space(SQLSTATE 01657), the statement is executed as DEFERRED DEFERRED The bufferpool size will be changed when the database is reactivated (all applications need to be disconnected from the database). Reserved memory space is not needed; DB2 will allocate the required memory from the system at activation time. NOT EXTENDED STORAGE Even if extended storage is enabled, pages that are being evicted from this buffer pool are not cached in extended storage EXTENDED STORAGE If extended storage is enabled, it can be used as a secondary cache for pages that are evicted from the buffer pool (Extended storage is enabled by setting the database configuration parameters NUM_ESTORE_SEGS and ESTORE_SEG SIZE to non-zero values ADD DATABASE PARTITION GROUP db-partition-group-name Adds this database partition group to the list of database partition groups to which the buffer pool definition is applicable. For any partition in the database partition group that does not already have the bufferpoo defined, the bufferpool is created on the partition using the default size specified for the bufferpool. Table spaces in db-partition-group-name may specify this buffer pool. The database partition group must currently exist the database(SQLSTATE 42704) NUMBLOCKPAGES number-of-pages Specifies the number of pages that should exist in the block-based area. The number of pages must not be greater than 98 percent of the number of pages for the buffer pool( SQLSTATE 54052 ). Specifying the value 0 disables block I/O. The actual value of nuMBloCKPageS used will be a multiple of BLOCKSIzE BLOCKSIZE number-of-pages Specifies the number of pages in a block. The block size must be a value between 2 and 256(SQLSTATE 54053). The default value is 32 Compatibilities For compatibility with previous versions of DB2: NODE can be specified in place of DBPARTITIONNUM
SIZE number-of-pages The size of the buffer pool specified as the number of pages. IMMEDIATE The bufferpool size will be changed immediately. If there is not enough reserved space in the database shared memory to allocate new space (SQLSTATE 01657), the statement is executed as DEFERRED. DEFERRED The bufferpool size will be changed when the database is reactivated (all applications need to be disconnected from the database). Reserved memory space is not needed; DB2 will allocate the required memory from the system at activation time. NOT EXTENDED STORAGE Even if extended storage is enabled, pages that are being evicted from this buffer pool are not cached in extended storage. EXTENDED STORAGE If extended storage is enabled, it can be used as a secondary cache for pages that are evicted from the buffer pool. (Extended storage is enabled by setting the database configuration parameters NUM_ESTORE_SEGS and ESTORE_SEG_SIZE to non-zero values.) ADD DATABASE PARTITION GROUP db-partition-group-name Adds this database partition group to the list of database partition groups to which the buffer pool definition is applicable. For any partition in the database partition group that does not already have the bufferpool defined, the bufferpool is created on the partition using the default size specified for the bufferpool. Table spaces in db-partition-group-name may specify this buffer pool. The database partition group must currently exist in the database (SQLSTATE 42704). NUMBLOCKPAGES number-of-pages Specifies the number of pages that should exist in the block-based area. The number of pages must not be greater than 98 percent of the number of pages for the buffer pool (SQLSTATE 54052). Specifying the value 0 disables block I/O. The actual value of NUMBLOCKPAGES used will be a multiple of BLOCKSIZE. BLOCKSIZE number-of-pages Specifies the number of pages in a block. The block size must be a value between 2 and 256 (SQLSTATE 54053). The default value is 32. Notes: v Compatibilities – For compatibility with previous versions of DB2: - NODE can be specified in place of DBPARTITIONNUM ALTER BUFFERPOOL Chapter 1. Statements 13
ALTER BUFFERPOOL NODEGROUP can be specified in place of DATABASE PARTITION GROUP Only the buffer pool size can be changed dynamically(immediately). All ther changes are deferred, and will only come into effect after the database is reactivated If the statement is executed as deferred, the following is true: Although the buffer pool definition is transactional and the changes to the buffer pool definition will be reflected in the catalog tables on commit, no changes to the actual buffer pool will take effect until the next time the database is started. The current attributes of the buffer pool will exist until then, and there will not be any impact to the buffer pool in the interim. Tables created in table spaces of new database partition groups will use the default buffer pool. The statement is IMMEDIATE by default when that keyword appl There should be enough real memory on the machine for the total of all the buffer pools, as well as for the rest of the database manager and application requirements. a buffer pool that is currently using extended storage cannot be altered to use block-based input/output(I/O). A buffer pool cannot be altered to use both extended storage and block-based I/O simultaneously 14 SQL Reference, Volume 2
- NODEGROUP can be specified in place of DATABASE PARTITION GROUP v Only the buffer pool size can be changed dynamically (immediately). All other changes are deferred, and will only come into effect after the database is reactivated. v If the statement is executed as deferred, the following is true: Although the buffer pool definition is transactional and the changes to the buffer pool definition will be reflected in the catalog tables on commit, no changes to the actual buffer pool will take effect until the next time the database is started. The current attributes of the buffer pool will exist until then, and there will not be any impact to the buffer pool in the interim. Tables created in table spaces of new database partition groups will use the default buffer pool. The statement is IMMEDIATE by default when that keyword applies. v There should be enough real memory on the machine for the total of all the buffer pools, as well as for the rest of the database manager and application requirements. v A buffer pool that is currently using extended storage cannot be altered to use block-based input/output (I/O). A buffer pool cannot be altered to use both extended storage and block-based I/O simultaneously. ALTER BUFFERPOOL 14 SQL Reference, Volume 2
ALTER DATABASE PARTITION GROUP ALTER DATABASE PARTITION GROUP The ALteR DATABASE PARTITION GROUP statement is used to add one or more partitions to a database partition group drop one or more partitions from a database partition group Invocation: This statement can be embedded in an application program or iss interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package(SQLSTATE 42509) Authorization: The authorization id of the statement must have SySctl or SYSaDM authorit M-ALTER DATABASE PARTITION GROJE ons-clause LLIKE DEPART db-portitton-number- THOJT TAB db-partitions-clause: 0-db-partition-number2 Description ition-name Names the database partition group. This is a one-part name. It is an SQL identifier(either ordinary or delimited). It must be a database partition group described in the catalog. IBMCATGROUP and IBMTEMPGROUP cannot be specified(SQLSTATE 42832) ADD DBPARTITIONNUM Specifies the specific partition or partitions to add to the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified partition must not already be defined in the database partition group(SQLSTATE 42728)
ALTER DATABASE PARTITION GROUP The ALTER DATABASE PARTITION GROUP statement is used to: v add one or more partitions to a database partition group v drop one or more partitions from a database partition group. Invocation: This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). Authorization: The authorization ID of the statement must have SYSCTRL or SYSADM authority. Syntax: ALTER DATABASE PARTITION GROUP db-partition-name , ADD DBPARTITIONNUM db-partitions-clause DBPARTITIONNUMS LIKE DBPARTITIONNUM db-partition-number WITHOUT TABLESPACES DROP DBPARTITIONNUM db-partitions-clause DBPARTITIONNUMS db-partitions-clause: ( , db-partition-number1 TO db-partition-number2 ) Description: db-partition-name Names the database partition group. This is a one-part name. It is an SQL identifier (either ordinary or delimited). It must be a database partition group described in the catalog. IBMCATGROUP and IBMTEMPGROUP cannot be specified (SQLSTATE 42832). ADD DBPARTITIONNUM Specifies the specific partition or partitions to add to the database partition group. DBPARTITIONNUMS is a synonym for DBPARTITIONNUM. Any specified partition must not already be defined in the database partition group (SQLSTATE 42728). ALTER DATABASE PARTITION GROUP Chapter 1. Statements 15