|
|
Dbname
specifies the name of the database for which you want to reorganize the
catalog indexes. Any initial substring for DBNAME can be used as the
keyword (for example DB or D).
dcssID
specifies the name of a bootstrap package that identifies a saved segment. You
can use DCSSID or ID for the keyword. If not specified, DCSSID defaults to
SQLDBA. The SQLDBA bootstrap package causes the database manager code
to be loaded into the DMSFREE area.
PARM
specifies additional DB2 Server for VM initialization parameters. If you specify
the PARM parameter, it must follow the other SQLCIREO parameters.
The valid initialization parameters are:
ARCHPCT=n
CHARNAME=name
CHKINTVL=n
DUMPTYPE=F|N|P
LOGMODE=A|L|N|Y
NCSCANS=n
NDIRBUF=n
NPAGBUF=n
PARMID=name
SLOGCUSH=n
SOSLEVEL=n
TRACCONV=n
TRACDBSS=nnnnnnnnnnn
TRACDRRM=nnnn
TRACDSC=nn
TRACEBUF=n
TRACRDS=nnnnnnn
TRACWUM=n
TRACSTG=n
Initialization parameters are described in the DB2 Server for VM System
Administration manual and the DB2 Server for VSE & VM Operation manual.
SQLCIREO automatically supplies the initialization parameters DBNAME
(based on what you supply in the EXEC parameter DBNAME), SYSMODE=S,
and STARTUP=I.
To avoid the processing involved in switching log modes, use the same
LOGMODE that you normally use.
Because the catalog index reorganization utility runs in single user mode, the
only way to trace it is with the TRACDBSS, TRACDSC, and TRACWUM
initialization parameters. (The TRACE operator command cannot be used in
single user mode.)
If you are using tracing, you may want to enter your own CMS FILEDEF and
LABELDEF commands before invoking SQLCIREO. These optional CMS
FILEDEF and LABELDEF commands are described in the VM/ESA: CMS
Application Development Reference for your VM system.
For example, to reorganize the indexes for the PRODX database, you might enter:
258
Database Administration
SQLCIREO D(PRODX) PARM(LOGMODE=A)
The catalog index reorganization utility directs messages to SYSPRINT. If you do
not supply a FILEDEF command for SYSPRINT, SQLCIREO assigns SYSPRINT to
the terminal. This FILEDEF command directs the utility’s messages to the CMS file
REORG PRT A:
FILEDEF SYSPRINT DISK REORG PRT A (RECFM FBA LRECL 121 BLOCK 1210
The utility prints informational messages that describe what actions it has taken.
Packages are not invalidated if they use an index reorganized by the SQLCIREO
utility.
If the catalog index reorganization utility abnormally ends, all changes it has made
to the database are rolled back. You should rerun the utility after correcting the
problem.
SQLRELEP EXEC
This EXEC releases empty pages in dbspaces. The following diagram shows the
format of the SQLRELEP EXEC.
►► SQLRELEP Dbname
( dbname
) dbspaceNUM
(
►
dbspacenum
)
► dcssID
(
) PARM
(
)
►◄
Figure 63. SQLRELEP EXEC
Dbname
specifies the name of the database for which you want to release empty pages.
Any initial substring for DBNAME can be used as the keyword (for example
DB or D).
dbspaceNUM
specifies the acquired dbspace for which you want to release the empty pages.
It can be a public, private, or system dbspace. By entering *, every dbspace will
be processed.
dcssID
specifies the name of a bootstrap package that identifies a saved segment. You
can use DCSSID or ID for the keyword. If not specified, DCSSID defaults to
SQLDBA. The SQLDBA bootstrap package causes the database manager code
to be loaded into the DMSFREE area.
PARM
specifies additional DB2 Server for VM initialization parameters. If you specify
the PARM parameter, it must follow the other SQLRELEP parameters.
The valid initialization parameters are:
ARCHPCT=n
CHARNAME=name
CHKINTVL=n
DUMPTYPE=F|N|P
LOGMODE=A|L|N|Y
NCSCANS=n
Appendix B. CMS EXECs
259
NDIRBUF=n
NPAGBUF=n
PARMID=name
SLOGCUSH=n
SOSLEVEL=n
TRACCONV=n
TRACDBSS=nnnnnnnnnnn
TRACDRRM=nnnn
TRACDSC=nn
TRACEBUF=n
TRACRDS=nnnnnnn
TRACWUM=n
TRACSTG=n
Initialization parameters are described in the DB2 Server for VM System
Administration and the DB2 Server for VSE & VM Operation manuals.
SQLRELEP automatically supplies the initialization parameters DBNAME (based
on what you supply in the EXEC parameter DBNAME), SYSMODE=S, and
STARTUP=P.
To avoid the processing involved in switching log modes, use the same
LOGMODE that you normally use.
Because the release empty pages utility runs in single user mode, the only way to
trace it is with the TRACDBSS, TRACDSC, and TRACWUM initialization
parameters. (The TRACE operator command cannot be used in single user mode.)
If you are using tracing, you may want to enter your own CMS FILEDEF and
LABELDEF commands before invoking SQLRELEP. These optional CMS FILEDEF
and LABELDEF commands are described in the VM/ESA: CMS Application
Development Reference for your VM system.
For example, to release the empty pages on dbspace 1 for the PRODX database,
you might enter:
SQLRELEP D(PRODX) NUM(1) PARM(LOGMODE=A)
The release empty pages utility directs messages to SYSPRINT. If you do not
supply a FILEDEF command for SYSPRINT, SQLRELEP assigns SYSPRINT to the
terminal. This FILEDEF command directs the utility’s messages to the CMS file
REORG PRT A:
FILEDEF SYSPRINT DISK REORG PRT A (RECFM FBA LRECL 121 BLOCK 121)
The utility prints informational messages that describe what actions
it has taken.
If the release empty pages utility abnormally ends, the changes made to the
directory cannot be rolled back because directory changes are never logged. To
ensure the consistency of the database, a restore should be done if a failure occurs
in SQLRELEP.
A database archive should be taken after the release empty pages utility completes
to ensure unexpected storage pool full situations do not occur during restore.
Consider the following example:
1. You take a database archive with LOGMODE=L
2. You release empty pages on dbspace A, preventing a potential short on storage
situation for storage pool X where it resides.
3. Inserts are made to tables in dbspace B, which is also in storage pool X.
260
Database Administration
4. A restore of the archive taken in step 1 is done. This forces a log archive.
After the database restore completes, the directory is at the same state as before
empty page processing was run. The log archive created in step 4 is applied but it
does not contain information about release empty pages processing. Therefore,
storage pool X is near a short on storage condition. When the insert statements are
applied from the log archive, the database manager needs to use free pages in
storage pool X. This could lead to a storage pool full condition that did not occur
earlier.
SQLDBID EXEC
The SQLDBID EXEC displays the name of the default application server that has
been established by the SQLINIT EXEC.
Figure 64 shows the format of the SQLDBID EXEC.
►► SQLDBID
►◄
LIFO
FIFO
Figure 64. SQLDBID EXEC
The SQLDBID EXEC resides on the production minidisk. The SQLDBID EXEC
displays the name of the default application server that will be accessed if the
resource adapter bootstrap on the A-disk is used.
The SQLDBID EXEC does no more than display information. If the name of the
application server displayed is not the one you want to access, you must rerun the
SQLINIT EXEC.
If you specify the LIFO or FIFO parameters, the information provided by the
SQLDBID EXEC is stacked onto the most recently created buffer of the program
stack (system provided data queue). If you specify the FIFO parameter, the
information is stacked on a first in, first out basis. If you specify the LIFO
parameter, the information is stacked on a last in, first out basis. If neither
parameter is specified, message ARI0320I, specifying the application server name,
is issued.
SQLRMEND EXEC
The SQLRMEND EXEC manages the communication links between an application
program and an application server. It does this in two ways:
v If you have more than one program called from an EXEC, SQLRMEND can
ensure the integrity of each program by dropping the link used by the current
program, or dropping the resource adapter code which drops all links between
all programs and all application servers they access
v If you have one program, SQLRMEND can drop the resource adapter code to
free storage from your virtual machine.
v If your program runs the CMS subset, a ROLLBACK or COMMIT of all LUWs is
prevented. This is to ensure that any LUWs in the native CMS MODE are not
affected.
Appendix B. CMS EXECs
261
Usually the resource adapter code and control blocks are not dropped and
outstanding work is not committed until the end of the VM command. When
programs are invoked from within EXECs, the “end of command” is at the end of
the EXEC, not the end of the program. If you want to drop the resource adapter
code or perform COMMIT/ROLLBACK processing at the end of a program (not at
the end of the EXEC), you must run the SQLRMEND EXEC. Figure 65 shows the
format of the SQLRMEND EXEC.
COMMIT
RELEASE
►► SQLRMEND
ROLLBACK
KEEP
►◄
ALL
Figure 65. SQLRMEND EXEC
The SQLRMEND EXEC resides on the production minidisk.
The SQLRMEND EXEC can be used to drop the DB2 Server for VM resource
adapter code and its control blocks from within the DMSFREE (user free storage)
area of a user’s virtual machine. It allows users who invoke more than one
program from within an EXEC to free the storage used by the resource adapter.
The storage can then be used by other programs.
The SQLRMEND EXEC can also be used to perform COMMIT/ROLLBACK
processing on all outstanding work. It allows users who invoke more than one
program from within an EXEC to COMMIT or ROLLBACK all outstanding work
before the next program is executed.
You may have separate logical units of work active at one time. You could do this
by having one or more programs running in separate CMS Work Units. When you
use SQLRMEND in this situation, the second option (RELEASE, KEEP, or ALL)
determines which CMS Work Units are affected.
Note: You cannot use COMMIT ALL or ROLLBACK ALL in CMS subset mode to
prevent the user from affecting any work done previously in a normal CMS
mode. The SQLRMEND EXEC will not allow these parameters in CMS
subset mode.
COMMIT RELEASE
is the default. Specifies the currently active LUW within the active work unit is
to be committed, the communication link to be released. Resource adapter code
is dropped only if there is just one CMS Work Unit.
If you have more than one CMS Work Unit, only the current LUW is
committed, and the link between the program and the database it is accessing
is dropped.
COMMIT KEEP
specifies the active LUW within the active work unit is to be committed and
the communication link and the resource adapter code are to be kept.
COMMIT ALL
specifies all active LUWs for all suspended and active work units are to be
committed, the communication link(s) to be released, and the resource adapter
code is to be dropped.
262
Database Administration
ROLLBACK RELEASE
specifies the active LUW in the active work unit is to be rolled back and the
communication link is to be released. Resource adapter code is dropped only if
there is just one CMS Work Unit.
If you have more than one CMS Work Unit, the current link between the
program and the application server it is accessing is dropped, and the current
logical unit of work is rolled back.
ROLLBACK KEEP
specifies the currently active LUW in the active work unit is to be rolled back
and that the communication link and the resource adapter code are to be kept.
ROLLBACK ALL
specifies all active LUWs for all suspended and active work units are to be
rolled back, the communication link(s) to be released, and the resource adapter
code is to be dropped.
COMMIT
is equivalent to COMMIT RELEASE.
RELEASE
is equivalent to COMMIT RELEASE.
ROLLBACK
is equivalent to ROLLBACK RELEASE.
KEEP
is equivalent to COMMIT KEEP.
ALL
is equivalent to COMMIT ALL.
The KEEP option will keep the communication link. Therefore if you want to
access an application server again from within the EXEC, your next program will
be able to use the same SQL connection (user ID and application server) without
issuing an explicit CONNECT.
Unless you are maintaining more than one CMS Work Unit, the RELEASE option
will drop the communication link and the resource adapter code. Only the storage
used for the resource adapter control blocks will be freed. If you want to access the
DB2 application server again from within the EXEC, you do not need to enter
anything to get the resource adapter code back into storage. Just invoke the DB2
Server for VM application. The resource adapter code will automatically be
reloaded.
If you can have more than one CMS Work Unit, the RELEASE option drops the
current link. Logical units of work in other CMS Work Units are still active.
Example
The following example is a portion of an EXEC that runs in a VM system, and
invokes two programs. The second program runs in a separate CMS Work Unit.
After it has completed, the SQLRMEND EXEC is invoked with the COMMIT ALL
option, which commits the logical units of work active in each program, drops
both links, and frees the storage used by the resource adapter.
•
•
•
EXEC SQLINIT DB(DB01)
←Set up access to DB01 in first work unit
SQLPROG1
←Run program that accesses DB01
Appendix B. CMS EXECs
263
EXEC SQLINIT DB(DB02)
←Set up access to DB02 in second work unit
SQLPROG2
←Run program that accesses DB02
EXEC SQLRMEND COMMIT ALL
←commits both logical units of work, drops
both links, and drops resource adapter
The SQLINIT EXEC is invoked to switch application servers for the second
program.
ARISDBHD EXEC
The ARISDBHD EXEC deletes SQL/DS HELP text, including administrator
supplied topics, for one or more languages. It does not delete the message
repository.
To run the ARISDBHD EXEC, you must have:
v Read access to the SQL/DS service minidisk or SFS directory
v Read access to the SQL/DS production minidisk or SFS directory
v The connect password for SQLDBA.
►► ARISDBHD Dbname(dbname)
►◄
The parameters of the ARISDBHD EXEC are as follows:
Dbname(dbname)
Replace dbname with the name of the database in which the HELP text is to
be deleted.
When you run the ARISDBHD EXEC:
1. Specify which HELP text languages to delete.
2. Confirm that you want to delete the HELP text for the specified languages.
3. Execute the delete procedure.
Step 1
When you invoke the ARISDBHD EXEC, you will be prompted for the
connect password for SQLDBA. After entering the password, the contents
of the SYSLANGUAGE table are reformatted and displayed. Specify the
language key for each language whose HELP text you wish to delete
(separated by commas or blanks) or ALL to specify all HELP text for all
languages in the database.
If you decide to stop the procedure at this point, enter QUIT.
Each time you select a language, it is flagged on the screen; a null entry
will process your selections.
Step 2
The languages that you indicated for deletion are displayed on the next
screen. Confirm that you want to delete all these languages by entering
YES. To exit from the procedure without deleting any HELP text languages,
enter NO.
Step 3
When prompted, enter the owner ID and virtual address of the CMS HELP
text for the language specified.
If you know that there is no CMS HELP text for the language specified,
enter SKIP to bypass the language.
264
Database Administration
If you accidentally delete the CMS HELP text for a different language by
entering the wrong virtual address, you can restore the environment by
executing the ARISDBMA EXEC for the deleted language and rerunning
this EXEC with the correct address.
If you wish to delete an entire language (both the messages and the HELP text),
use the ARISDBLD EXEC instead. The ARISDBHD EXEC deletes both ISQL help
and CMS help. This EXEC displays a list of currently installed languages, which
may contain languages that have already had their help deleted but are still active
(that is, are listed in the SYSLANGUAGE table and have the appropriate message
repository available). You will be prompted to select the HELP text languages for
deletion. You should not specify languages whose HELP text has already been
deleted. Since this EXEC cannot be used to delete a message repository, the
SYSLANGUAGE table and the ARISNLSC MACRO will not be updated in any
way. This EXEC will not affect the default language setting.
ARISDBLD EXEC
The ARISDBLD EXEC deletes the SQL/DS messages and HELP text, including
administrator supplied topics, for one or more languages.
To run the ARISDBLD EXEC, you must have:
v Read access to the SQL/DS service minidisk or SFS directory
v Read access to the SQL/DS production minidisk or SFS directory
v The connect password for SQLDBA.
►► ARISDBLD Dbname(dbname)
►◄
The parameters of the ARISDBLD EXEC are as follows:
Dbname(dbname)
Replace dbname with the name of the database in which the messages and
HELP text are to be deleted.
When you run the ARISDBLD EXEC it prompts you to:
1. Specify which languages to delete
2. Determine which is to become the new default language, if the current default
language is to be deleted, and two or more languages will remain on the
system
3. Confirm that you want to delete the messages and HELP text for the specified
languages.
Step 1
When you invoke the ARISDBLD EXEC, you will be prompted for the
connect password for SQLDBA. After entering the password, the contents
of the SYSLANGUAGE table are reformatted and displayed. Specify the
language key for each language whose HELP text you wish to delete
(separated by commas or blanks).
If you decide to stop the procedure at this point, enter QUIT.
Each time you select a language, it is flagged on the screen; a null entry
will process your selections.
Step 2
To delete the current default language with two or more languages
Appendix B. CMS EXECs
265
remaining on the system, you must specify which of the remaining
languages will be the new default language. When the current default
language is flagged for deletion, a list of the remaining languages and keys
is displayed. Specify the key for the new default language.
If only one language remains on the system, it will automatically become
the new default language.
Step 3
The languages that you flagged for deletion are displayed on the next
screen. Confirm that you want to delete all these languages by entering
YES. To exit from the procedure without deleting any HELP text languages,
enter NO.
Step 4
When prompted, enter the owner ID and virtual address of the CMS HELP
text for the language specified.
If you know that there is no CMS HELP text for the language specified,
enter SKIP to bypass the language.
If you accidentally delete the CMS HELP text for a different language by
entering the wrong virtual address, you can restore the environment by
executing the ARISDBMA EXEC for the deleted language and rerunning
this EXEC with the correct address.
A minimum of one language must be left on the ARISNLSC macro and the
SQLDBA.SYSLANGUAGE table; it is not possible to delete all the languages.
For each language being deactivated, the following occurs:
v The ARISNLSC MACRO is updated. If the current default language has been
marked for deletion, you must specify a new default language.
v The message repository for this language is deleted from the production
minidisk or directory.
v The ISQL HELP text is deleted by updating the SYSTEXT2 table.
v The ARISDBMC EXEC is invoked to delete CMS help.
SQLLEVEL EXEC
The SQLLEVEL EXEC displays the SQL/DS release level that is installed. For
example:
*** SQL/DS VERSION 7 RELEASE 1 MODIFICATION 0 ***
To run the SQLLEVEL EXEC, you must have:
v Read access to the SQL/DS service minidisk or SFS directory
v Read access to the SQL/DS production minidisk or SFS directory.
►► SQLLEVEL
►◄
RELMOD
The parameters for the SQLLEVEL EXEC are as follows:
RELMOD
If you include this parameter, the EXEC places the version, release, and
modification levels in the CMS stack. (These values are all integers.)
266
Database Administration
Appendix C. Querying the Status of an Application (VM Only)
SQLQRY is implemented as a CMS immediate command and enables you to query
the status of the application that you are currently running on the user machine. It
is initialized by the DB2 Server for VM resource adapter when the first database
statement is processed, and can be issued while your application is running. Since
it is implemented as a CMS immediate command, it can be used even when your
application is not accepting other input. See the VM/ESA: CMS User’s Guide
manual for more information about CMS immediate commands.
The SQLQRY command is particularly useful if problems arise while you are
switching between application servers. In these cases, use SQLQRY to determine
the application server to which you are currently connected.
Notes:
1. You can only enter the SQLQRY command from the user machine after the
resource manager has been loaded and while an application is running.
2. You cannot use the SQLQRY command if you are using the SYNChronous(YES)
option with the SQLINIT EXEC, if you are not receiving CP messages (for
example, if you specified CP SET MSG OFF), or if your application has locked
the keyboard. See “SQLINIT EXEC” on page 243 for information on the
SYNChronous(YES) option.
The following information is displayed at the terminal when you enter the
SQLQRY command:
EXTNAME
displays the user ID of the application requester to which you are currently
connected. It also displays the CMS Work Unit number, if CMS Work Units
are in use.
RDBMS
displays the name, class, and release level (version, release, and
modification level) of the application server being accessed. If the
Protocol(DRDA) or Protocol(AUTO) option is specified with the SQLINIT
EXEC and the SQLQRY command is issued before handshaking (the
process of establishing a connection) is completed, “n/a” will be displayed
for both the class and release level of the application server. If the
Protocol(SQLDS) option is specified with the SQLINIT EXEC and the
SQLQRY command is issued, “SQLDS/VM” will be displayed for the
application server class and “n/a” will be displayed for the application
server release level, because handshaking does not take place. See
“SQLINIT EXEC” on page 243 for information on the Protocol parameter.
STATUS
displays the communication state. COMM indicates that the Work Unit
sent an SQL statement to the database machine and has been waiting for a
reply since the time shown. APPL indicates that the Work Unit returned
control to the application at the time shown. VRA indicates that the VM
Resource Adapter is processing your request.
LUWID
displays the logical unit of work identifier, which uniquely identifies an
LU6.2 conversation. Its value is
netid.luname.instance_number.sequence_number, where netid and luname are up
267
to 8 characters long, instance_number is 12 characters long, and
sequence_number is 4 characters long. LUWID is only used for conversations
that use the AUTO and DRDA Protocol options. If the middle portion of
the LUWID contains *IDENT, then the application server is a local one or
is in a TSAF collection; in these cases, no LU name and TPN are displayed.
If TCP/IP is being used, the LUWID has the format
IPADDRESS.PORT.INSTANCE_NUMBER, where IPADDRESS is 8
characters long, PORT is 4 characters long, and INSTANCE_NUMBER is 12
characters long.
LU displays the logical unit name, if the access is through VTAM.
TPN displays the transaction program name. Its character and hexadecimal
versions are both displayed. For a DB2 application server, this is the same
as the resource ID.
TCPIP displays the IP address of the target host system. It is only displayed when
TCP/IP is being used.
PORT displays the port number of the target application server. It is only
displayed when TCP/IP is being used.
Example
Figure 66 displays sample output from an SQLQRY command issued in a VM/ESA
environment with Protocol(AUTO) and two active CMS Work Units.
11:09:51
* MSG FROM SQLUSR6 : Status of Server Conversations on 2000-09-20.
11:09:51
* MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.1
11:09:51
* MSG FROM SQLUSR6 : RDBMS
= SQLRDB1
SQLDS/VM V6.1.0
11:09:51
* MSG FROM SQLUSR6 : STATUS = COMM TIME = 2000-09-20.11:09:43
11:09:51
* MSG FROM SQLUSR6 : LUWID = IBMNET01.*IDENT.45F2ABCD236D42.0001
11:09:51
* MSG FROM SQLUSR6 :
11:09:51
* MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.2
11:09:51
* MSG FROM SQLUSR6 : RDBMS
= IBMSTLDB2
DB2
V2.3.0
11:09:51
* MSG FROM SQLUSR6 : STATUS = APPL TIME = 2000-09-20.11:07:32
11:09:51
* MSG FROM SQLUSR6 : LU = STLMVS04
TPN = "6DB
(X’07F6C4C2’)
11:09:51
* MSG FROM SQLUSR6 : LUWID = IBMNET01.TORLU001.45F2ABCD236DFE.0001
11:09:51
* MSG FROM SQLUSR6 :
11:09:51
* MSG FROM SQLUSR6 : EXTNAME = SQLUSR6.3
11:09:51
* MSG FROM SQLUSR6 : RDBMS
= SQLMACGM
SQLDS/VM V7.1.0
11:09:51
* MSG FROM SQLUSR6 : STATUS = COMM TIME = 2000-09-20.11:07:32
11:09:51
* MSG FROM SQLUSR6 : TCPIP = 9.21.23.32
PORT = 8030
11:09:51
* MSG FROM SQLUSR6 : LUWID = G9151720.L372.B1622DADEF8A
Figure 66. Sample Output from SQLQRY in a VM Environment with Protocol(AUTO) and
CMS Work Units
You can have multiple active CMS Work Units in a user machine, each accessing
an application server. In this example, information is displayed for two CMS Work
Units.
EXTNAME contains the user ID of the application requester, concatenated with the
CMS Work Unit number.
Because Protocol(AUTO) is used, a unique LUWID is assigned to each
conversation.
v Work Unit #1:
268
Database Administration
- Accesses a SQL/DS V3.3.0 application server called SQLRDB1. Since no LU
name or TPN are displayed, the application server must be a local one or is in
a TSAF collection. For the same reason, the middle portion of the LUWID is
*IDENT.
- Has a STATUS of COMM, indicating that it must have sent an SQL statement
to the application server and has been waiting for a reply (since 11:09:43, as
indicated by TIME).
v Work Unit #2:
- Accesses a DB2* V2.3.0 application server called IBMSTLDB2. The LU name
and TPN are displayed because the application server is in a VTAM network.
The DB2 application server uses the default DRDA TPN of X'07F6C4C2'.
- Has a STATUS of APPL, indicating that it has already returned control to the
application (at 11:07:32, as indicated by TIME).
v Work Unit #3:
- Accesses a DB2 Server for VM Version 7 Release 1 application server called
SQLMACGM. The HOST name and SERVICE port connection are displayed
because the application server is in a TCP/IP network.
Appendix C. Querying the Status of an Application (VM Only)
269
270
Database Administration
Appendix D. Maximums
The following tables describe logical data maximums and ISQL maximums.
Information about database maximums and system maximums can be found in the
DB2 Server for VM System Administration or DB2 Server for VSE System
Administration manual.
Table 35. Logical Data Maximums
Restricted Parameter
Maximum
Number of Tables per Database
8,000,000
Number of Indexes per Database
8,000,000
Number of Views per Database1
2,549,490
Number of Programs per Database1
2,549,490
Number of Tables per DBSPACE4
255
Number of Indexes per Table
255
Number of Columns per Table
255
Number of unique CCSID combinations per Table
80
Number of Columns per View3
≈ 140
Number of Columns in a SELECT-list
255
Length of a Row in a Table (Bytes)2
4,080
Number of Columns in an Index
16
Length of an Index Key (Bytes)
255
Number of Foreign Keys per Table
32,767
Notes:
1. The number of views plus the number of programs cannot exceed 2,549,490. This limit
assumes that you create the maximum number of dbspaces possible (9998) for
packages. Each dbspace can contain 255 packages. If you only create 10 dbspaces for
packages, you only have room for 2,550 packages.
2. Not including long field columns.
3. There is no specific limit on the number of columns in a view, because it depends on
many factors which affect this limit. A view of up to 140 columns should work in most
situations.
4. This maximum includes tables implicitly created as well as user-defined tables. Each
table with one or more long fields requires a table created implicitly to hold the long
fields. Long fields are LONG VARCHAR, LONG VARGRAPHIC, VARCHAR(n) where
n > 254, and VARGRAPHIC(n) where n > 127.
ISQL Maximums
Table 36. ISQL Maximums
Restricted Parameter
Maximum
Maximum number of columns in a query
45
Maximum length of a command (bytes)
2,048
The maximum number of columns in a query may be further reduced by the
width of the columns selected.
271
272
Database Administration
Appendix E. SQLGLOB Parameters (VSE Only)
DB2 Server for VSE stores certain environmental parameters in a VSAM file called
“SQLGLOB.” ISQL, DBSU and the preprocessors retrieve the CHARNAME and
DBCS values from this SQLGLOB file. The online and batch Resource Adapters
also access this file to determine certain environmental parameters as they
communicate with a remote application server.
The SQLGLOB VSAM file will hold both GLOBAL and USER parameters. There is
one set of global SQLGLOB parameters, which is the system-wide default values.
These global parameters are initially set with the IBM-supplied default values
during installation using the ARISGDEF procedure and subsequently updated
using the DSQG transaction. A CICS user can choose to override the global
SQLGLOB parameters by setting up his or her own user SQLGLOB parameters.
This is done by executing the DSQU transaction. There is one set of user
SQLGLOB parameters for every CICS user who executed the DSQU transaction. A
batch user can choose to override the global SQLGLOB parameters by setting up
their own user SQLGLOB parameters. This is done by executing the program
ARIRBGUD (JCL: ARISBGUD.Z) and specifying the Update (U) command and a
user ID. The ARIRBGUD program can also be used to update the global SQLGLOB
parameters, to query one of the user’s parameters or all of the user’s parameters,
or to delete a user’s set of parameters.
The SQLGLOB VSAM file is defined to the system and initially updated with the
IBM-supplied default global SQLGLOB parameter values during product
installation.
The SQLGLOB parameters and their initial IBM-supplied global default values are
described below:
QryBlksize
Specifies the block size used to return rows of data when DRDA blocking is
used to perform FETCHes or the block size of the buffer that will carry Inserts
to the database server. The number is specified in denominations of 1K and
can range anywhere between 1K and 32K.
This option is only used when the application requester is communicating with
a remote application server.
The IBM-supplied global default QryBlksize is 8K.
CHARNAME
Specifies the character set name, which determines the CCSID values for
CCSIDSBCS, CCSIDMIXED and CCSIDGRAPHIC used by the application
requester, and determines how ISQL and the preprocessors fold characters
from lowercase to uppercase. Its value must be a valid character set name,
such as those found in the CHARNAME column of the SYSTEM.SYSCCSIDS
table.
This value is used by ISQL, DBSU, and the preprocessors instead of the value
currently found in the SYSTEM.SYSOPTIONS table.
The IBM-supplied global default CHARNAME is INTERNATIONAL.
DBCS
Specifies whether DBCS character handling of SO/SI pairs is done or not. This
273
value is used by ISQL, DBSU, and the preprocessors instead of the value
currently found in the SYSTEM.SYSOPTIONS table.
YES
Specifies that error checking is done on DBCS data by the preprocessors,
DBSU and ISQL. If double byte characters are to be used, DBCS must be
set to YES.
NO
Specifies that error checking is not done on DBCS data by the
preprocessors, DBSU and ISQL.
The IBM-supplied global default DBCS is NO.
SYNCPOINT
Specifies how commits or rollbacks are to be coordinated by the CICS/VSE
syncpoint manager.
1
Specifies a one-phase commit is to be done. In this case, the CICS/VSE
sync point manager is not involved and unprotected APPC conversations
are used.
2
Specifies a two-phase commit is to be done. In this case, protected APPC
conversations will be used to connect to the DRDA server and the
CICS/VSE sync point manager will be used to coordinate two-phase
commits. If a user is updating a remote server and other CICS resources
which participate in two-phase commit within a logical unit of work,
SYNCPOINT must be set to 2.
This option is only used when the online CICS application requester is
communicating with a remote application server through SNA.
The IBM-supplied global default SYNCPOINT is 1.
TRACERA
Specifies a two digit number (nn) which specifies the parts of the Batch and
Online Resource Adapter that are to be traced and the level of the trace. Trace
data is collected only when the application is connected to a remote server. The
positional digits correspond to the following Resource Adapter subcomponents
and functions:
v Resource Adapter control flow
v Communications.
0
Tracing is turned off.
1
Tracing is done in limited detail.
2
Tracing is done in greater detail.
TRACERA is ignored on local connections.
The IBM-supplied global default TRACERA is 00.
TRACEDRRM
Specifies a four digit number (nnnn) which specifies the parts of the DRRM
component that are to be traced and the level of the trace. Trace data is
collected only when the application is connected to a remote server. The
positional digits correspond to the following DRRM subcomponents and
functions:
v Parser
274
Database Administration
v Generator
v Dictionary
v RDIIN Manager.
0
Tracing is turned off.
1
Tracing is done in limited detail.
2
Tracing is done in greater detail.
TRACEDRRM is ignored on local connections.
The IBM-supplied global default TRACEDRRM is 0000.
TRACECONV
Specifies a 1 digit number (n) which specifies that the data conversion
component is to be traced and the level of the trace. Trace data is collected
only when the application is connected to a remote server.
0
Tracing is turned off.
1
Tracing is done in limited detail.
2
Tracing is done in greater detail.
TRACECONV is ignored on local connections.
The IBM-supplied global default TRACECONV is 0.
Communications Protocol
Specifies which network access method (SNA or TCP/IP) to use for remote
connections.
S Specifies that SNA is used for the connection to the remote application
server. In this case, the remote application server entry in the DBNAME
Directory should contain SNA routing information (SYSID and REMTPN).
S is the default for this field.
T Specifies that TCP/IP is used for the connection to the remote application
server. In this case, the remote application server entry in the DBNAME
Directory should contain TCP/IP routing information (TCPPORT and
TCPHOST, or TCPPORT and IPADDR).
This option is only used when the online CICS application requester is
communicating with a remote application server. Note that Batch Applications
must always use TCP/IP to communicate with a remote server.
The IBM-supplied global default COMMUNICATIONS PROTOCOL is SNA.
Transactions for Updating SQLGLOB Parameters
This section describes the various CICS transactions that a user can use to manage
parameters stored in the SQLGLOB VSAM file.
DSQG - Update global SQLGLOB Parm Transaction
The DSQG transaction is a CICS transaction which updates a subset of the global
SQLGLOB parameters.
Appendix E. SQLGLOB Parameters (VSE Only)
275
This must be defined as a secured transaction. That is, this transaction must be
defined with a TRANSEC value greater than 1, so that it cannot be initiated by any
user on the CICS system. Only authorized CICS users should be allowed to invoke
this transaction.
This transaction has five parameters as shown in the following syntax diagram. See
Appendix E, “SQLGLOB Parameters (VSE Only),” on page 273 for more
information on these DSQG parameters.
►► DSQG
,
,
,
,
►
CHARNAME,
QryBlksize,
DBCS,
SYNCPOINT,
►
►◄
Communications Protocol
Each time the DSQG transaction is executed, the global SQLGLOB parameters are
replaced as follows:
1. If the user specifies a parameter on the DSQG transaction, that parameter value
is used.
2. If a parameter is not specified on the DSQG transaction, the current global
SQLGLOB parameter is used.
The global SQLGLOB parameters remain in effect until they are explicitly changed.
The global SQLGLOB parameters can be changed by invoking the DSQG
transaction and specifying the new parameters, or by invoking program
ARIRBGUD (the SQLGLOB File Batch Update/Query Program) and specifying the
Update (U) command with the user ID *SYSDEF*.
DSQU - Update user SQLGLOB Parm Transaction
The DSQU transaction is a CICS transaction which initializes the SQLGLOB
parameters for the signed-on user ID. The user SQLGLOB parameters, like the
global SQLGLOB parameters, are stored in the SQLGLOB VSAM file.
This transaction has eight parameters, as shown in the following syntax diagram.
See Appendix E, “SQLGLOB Parameters (VSE Only),” on page 273 for more
information on these DSQU parameters.
►► DSQU
,
,
,
,
►
CHARNAME,
QryBlksize,
DBCS,
SYNCPOINT,
►
,
,
,
►◄
TRACERA,
TRACEDRRM,
TRACECONV,
Communications Protocol
Each time the DSQU transaction is executed, the user SQLGLOB parameters are
replaced. When a user reissues the DSQU transaction, the parameter value is
established as follows:
1. If the user specifies a parameter on the DSQU transaction, that parameter value
is used.
2. If a parameter is not specified on the DSQU transaction, the current user
SQLGLOB parameter is used. That is, the default is the value used on the most
recent DSQU transaction. However, if the user SQLGLOB parameter does not
exist, the global SQLGLOB parameter is used.
276
Database Administration
The user SQLGLOB parameters remain in effect until they are explicitly changed or
until they are explicitly deleted through a subsequent DSQD transaction. The user
SQLGLOB parameters can be changed by invoking the DSQU transaction and
specifying the new parameters or by invoking the program ARIRBGUD (SQLGLOB
File Batch Update/Query Program) and specifying the Update (U) command with
the appropriate user ID and new parameters. The user SQLGLOB parameters can
be deleted by invoking the DSQD transaction or by invoking the program
ARIRBGUD and specifying the Delete (D) command with the appropriate user ID.
All DRDA connections initiated by online CICS transactions, except those initiated
by ISQL, CBND, or any task that was started by the EXEC CICS START command,
will use the signed-on user’s SQLGLOB parameters, if they exist. If they do not
exist, these DRDA connections will use the global SQLGLOB parameters.
ISQL, CBND, and any task that was started by the EXEC CICS START command
will use the global SQLGLOB parameters for DRDA connections regardless of who
is signed on, with the following exceptions:
1. ISQL uses the user DBCS parameter to determine whether DBCS character
handling is required or not.
2. ISQL uses the user CHARNAME parameter to get the folding table to fold
input from the terminal from lowercase to uppercase, but it uses the global
CHARNAME for CCSID data conversion.
All DRDA connections initiated by VSE batch application programs will use the
SQLGLOB parameters (if they exist) of the user ID specified on the SQL
CONNECT statement. If they do not exist, these DRDA connections will use the
global SQLGLOB parameters.
DSQQ - Query SQLGLOB Parm Transaction
The DSQQ transaction is a CICS transaction which displays all the SQLGLOB
parameters. Which version of the SQLGLOB parameters (user or global) is
displayed depends on whether or not the userid parameter is specified on the
DSQQ command.
This transaction has one parameter, as shown in the following syntax diagram:
►► DSQQ
►◄
userid
userid
Specifies the user ID whose user SQLGLOB parameters are to be displayed.
If the userid parameter is specified and the user SQLGLOB parameters of the
specified userid exist, DSQQ will display the user SQLGLOB parameters.
If the userid parameter is omitted, DSQQ will display the global SQLGLOB
parameters.
DSQD - Delete user SQLGLOB Parm Transaction
The DSQD transaction is a CICS transaction which deletes a signed-on user ID’s
user SQLGLOB parameters.
This transaction has no parameters, as shown in the following syntax diagram:
Appendix E. SQLGLOB Parameters (VSE Only)
277
►► DSQD
►◄
After the signed-on user’s user SQLGLOB parameters are deleted, any subsequent
DRDA connections done on behalf of this signed-on user ID will use the global
SQLGLOB parameters.
Batch Program to Update/Query the SQLGLOB File
If a “CONNECT user ID” is needed for a remote server, but the user ID does not
exist as a CICS user ID, then a batch program and JCL are supplied to allow the
SQLGLOB file to be updated for any user ID. This stand-alone program,
ARIRBGUD, allows a new user ID to be inserted or an existing user ID to be
updated, deleted or queried based on the input given to the program. Input for
this program is provided from SYSIPT “cards” (80 byte records).
Table 37 describes the layout of the input for ARIRBGUD:
Table 37. ARIRBGUD Input Layout. Description
Start —
Field Name
Usage
End
Column
#s
1-1
COMMAND
’Q’ = query, ’D’ = delete, or ’U’ = update. This field is
mandatory.
3 - 10
USERID
This is the user ID in the SQLGLOB file that is to be operated
upon. This can be ’*’ if COMMAND is ’Query’. This field is
mandatory with an 8 byte maximum length.
12 - 29
CHARNAME
Character Set Name; it determines CCSID values and folding (18
bytes maximum).
31 - 32
QRYBLKSIZE
Block size used for Blocked Fetches, in integral ’K’ bytes, from 1
to 32.
34 - 34
DBCS
’Y’ if DBCS is allowed. ’N’ if DBCS is not allowed.
36 - 36
SYNCPOINT
’1’ or ’2’, for one- or two-phase COMMIT (DRDA 1 or DRDA 2).
This field is only used by the CICS Application Requester for
remote DRDA connections.
38 - 39
TRACERA
Resource Adapter Trace flags, two digits, each either ’0’, ’1’ or ’2’.
41 - 44
TRACEDRRM DRRM Trace flags, four digits, each either ’0’, ’1’ or ’2’.
46 - 46
TRACECONV CONV Trace Flags, 1 digit, either ’0’, ’1’ or ’2’.
48 - 48
COMMPROTOCommunications Protocol to be used for Remote database access:
’S’=SNA or ’T’=TCP/IP. Only used by CICS , not Batch.
Notes:
1. The COMMAND and USERID are always required.
2. For the Query COMMAND only, the user ID can be specified as ’*’, which
means display all user IDs in the SQLGLOB file.
3. For a Query or Delete COMMAND, all other fields are ignored.
4. For an Update COMMAND, fields that are left blank remain unchanged in an
existing record for the user ID. If the user ID does not already have an existing
record in SQLGLOB, then the System Default record values are used.
5. The System Default record (user ID = X'FF's) CANNOT be deleted by this
program. It CAN be updated or queried. Considering this user ID cannot be
278
Database Administration
displayed, it is displayed as the string ″*SYSDEF*″, and this string must be
used for the Update or Query COMMAND as the ″CONNECT User ID″.
|
6. Global SQLGLOB record parameters TRACERA, TRACEDRRM, TRACECONV
|
cannot be updated using this program, even if string ″*SYSDEF*″ is used as the
|
″CONNECT User ID″.
The following is an example of the SYSLST output from the program ARIRBGUD,
with a default of 120 print positions per print line. Input records are shown in bold
italics. Note that the first byte of the listing is a printer carriage control character.
1ARIRBGUD - Batch Query/Update of DB2 for VSE SQLGLOB File
1999/12/31 23:59:59
0
CCSID CCSID CCSID QRYBLK
SYNC
----TRACE---
COMM
COMMAND USER
CHARNAME
SBCS MIXED DBCS
SIZE DBCS POINT RA DRRM CONV
PROTO
0---—> Q UUUUUUUU
QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss
12K Y
1
00 0000
0
TCPIP
0---—> U UUUUUUUU
32 N 2 11 2222
S
UPDATE UUUUUUUU cccccccccccccccccc sssss sssss sssss
32K N
2
11 2222
0
SNA
0---—> D UUUUUUUU
DELETE UUUUUUUU
0---—> Q UUUUUUUU
ARI0485I The user SQLGLOB parameters for user UUUUUUUU do not exist.
0---—> U BADPARM
99
ARI0494E Invalid input parameter entered. Parameter = QRYBLKSIZE.
0---—> X JUNK STUFF
ARI4599E Invalid COMMAND given, must be ’Q’(Query), ’U’(Update) or ’D’(Delete).
0---—> D *SYSDEF*
ARI4598E You can not DELETE the system default record from the SQLGLOB file.
0---—> Q *
---—> List of all Users in the SQLGLOB file:
QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss bbbbb d
s tt tttt t
ppppp
QUERY uuuuuuuu cccccccccccccccccc sssss sssss sssss bbbbb d
s tt tttt t
ppppp
QUERY
*SYSDEF* cccccccccccccccccc sssss sssss sssss bbbbb d
s tt tttt t
ppppp
The following is an example of the SYSLST output from ARIRBGUD, because of a
JCL error or because the file has never been created:
1ARIRBGUD - Batch Query/Update of DB2 for VSE SQLGLOB File
1999/12/31 23:59:59
0ARI0487E The SQLGLOB file does not exist.
The JCL to execute the program ARIRBGUD (SQLGLOB File Batch Update/Query
Program) can be found in the IBM-supplied job ARISBGUD.Z.
Using Online and Batch Resource Adapter Tracing
The online (CICS) and batch Resource Adapter tracing is used for problem
analysis. You would normally not turn tracing on unless requested to do so by
IBM Support. The Online Resource Adapter trace output can be directed to a tape
or a disk file, which must be defined in the CICS start up JCL and predefined in
the CICS Destination Control Table. See the DB2 Server for VSE Program Directory
Appendix E. SQLGLOB Parameters (VSE Only)
279
for details about setting up the Destination Control Table. The batch Resource
Adapter trace can only be directed to a tape file, which must be defined in the
batch job JCL. Tracing is only performed for application statements that access
remote DRDA servers.
|
Tracing is activated by updating the SQLGLOB file record for the SQL CONNECT
|
statement user IDs (or the default Online user IDs). A ’1’ or ’2’ character placed in
|
the TRACERA, TRACEDRRM or TRACECONV component field activates tracing
|
for the respective subcomponents. A ’0’ character in this field deactivates tracing.
|
You can use the DSQU CICS transaction or the ARIRBGUD program (described in
|
“Batch Program to Update/Query the SQLGLOB File” on page 278) to update the
|
SQLGLOB file records.
Online Trace File JCL
The following is JCL that must be placed in the CICS start up JCL to define the
trace output file:
v For a tape file:
// ASSGN SYS018,181
// TLBL ARITRAC,’name of trace file’
v For a disk file:
// ASSGN SYS018,DISK,VOL=volxxx,SHR
// DLBL ARITRAC,’name of trace file’,0,SD
// EXTENT SYS018,volxxx,n,n,nnn,nn
Batch Trace File JCL
The following is JCL that must be placed in the batch job JCL to define the trace
output file:
// ASSGN SYS005,181
// TLBL ARITRAC,’name of trace file’
Formatting the Online or Batch Trace File
After the trace is finished, you can invoke the trace formatting utility that comes
with the DB2 Server for VSE system. The above JCL must also be used when the
trace file is used as input to the trace formatter utility, except ’SYS004’ must be
used instead of ’SYS005’ or ’SYS018’ when your trace file is on tape. ’SYS018’ must
be used when your Online trace file is on disk. Note that the Batch trace file
cannot be on disk. See ″Formatting DB2 Server for VSE Trace Output″ in DB2
Server for VSE & VM Operation.
Before the Online Resource Adapter trace file can be formatted, it must be closed
using the following CICS command:
CEMT SET QUEUE(ARIT) DISABLED CLOSED
Before the batch Resource Adapter trace file can be formatted, it must be closed
using the following JCL statement ″after″ the batch job step being traced:
// MTC WTM,SYS005,2
280
Database Administration
Appendix F. Preparing the Schema Stored Procedures for
CLI/ODBC/JDBC/OLE DB Client Applications
The schema stored procedures are invoked internally by CLI/JDBC drivers. You
need to prepare the schema stored procedures before the database can be accessed
by CLI, ODBC, JDBC, or OLE DB client applications using DB2 UDB Version 8 or
later.
The following files are provided for the schema stored procedures:
Table 38. Schema Stored Procedures Files
Schema Stored Procedure
Name
TEXT/OBJECT File Name
Package File Name
SQLCOLPRIVILEGES
ARIXU01A
ARIXU01B
SQLCOLUMNS
ARIXU02A
ARIXU02B
SQLFOREIGNKEYS
ARIXU03A
ARIXU03B
SQLPRIMARYKEYS
ARIXU04A
ARIXU04B
SQLPROCEDURECOLS
ARIXU05A
ARIXU05B
SQLPROCEDURES
ARIXU06A
ARIXU06B
SQLSPECIALCOLUMNS
ARIXU07A
ARIXU07B
SQLSTATISTICS
ARIXU08A
ARIXU08B
SQLTABLEPRIVILEGES
ARIXU09A
ARIXU09B
SQLTABLES
ARIXU10A
ARIXU10B
SQLGETTYPEINFO
ARIXU11A
ARIXU11B
SQLUDTS
ARIXU12A
ARIXU12B
Setting up Schema Stored Procedures for CLI/ODBC/JDBC/OLE DB
Client Applications
You must have at least one stored procedure server defined before running this
process. For information about setting up a stored procedure server, see “Setting
up a Stored Procedure Server” on page 210. For information about defining a
stored procedure server, refer to the ″CREATE PSERVER″ section of the DB2 Server
for VSE & VM SQL Reference manual.
For DB2 VM Server:
Run the IBM-supplied DBSUs, ARISPDEF MACRO and ARISPRLD MACRO. You
need to run the DBSUs when you are setting up the schema stored procedures for
the first time, unless instructions in the PTF state otherwise.
To run this DBSUs, you must:
1. Ensure that the connect ID has DBA authority.
2. Make appropriate changes to these DBSUs for your specific environment.
ARISPDEF:
281
v Defines tables needed (in addition to the system catalog tables) for the schema
stored procedures.
v Defines each of the schema stored procedures to the SYSTEM.SYSROUTINES
catalog table.
Once the DBSU runs successfully, you need to bind stored procedure bindfile(s)
against the target server to create the package. After successful binding, grant RUN
authority on the packages of the schema stored procedures to PUBLIC.
For DB2 VSE Server:
Run the IBM-supplied JCLs, ARISPGPH.Z, ARISPCTB.Z, and ARISPSET.Z. You
need to run the two jobs when you are setting up the schema stored procedures
for the first time. You only need to run ARISPGPH after applying service to the
schema stored procedure(s), unless instructions in the PTF state otherwise.
To run these jobs, you must:
1. Ensure that the connect ID has DBA authority.
2. Make appropriate changes to these JCLs for your specific environment.
ARISPGPH creates phase for each of the schema stored procedures.
ARISPCTB:
v Defines tables needed (in addition to the system catalog tables) for the schema
stored procedures.
v Defines each of the schema stored procedures to the SYSTEM.SYSROUTINES
catalog table.
After completion of above steps, you need to bind stored procedure
bindfile(bind-records) against the target server to create the required packages.
After successful binding, grant RUN authority on the packages of the schema
stored procedures to PUBLIC. In VSE, you must load stored procedure bindfiles
provided as A-member to VSAM bindfile. For more information on storing the
bind files in the VSAM bind file, see DB2 Server for VSE Program Directory.
For both DB2 VSE and VM servers, if you are using a LOCAL date or time format
with a different length than ISO, USA, EUR, and so on, you need to run the
IBM-supplied program ARIXUPTB before running the schema stored procedures
for the first time. You do not need to run this program again, as long as the length
of the date or the time format remains unchanged.
Notes:
1. DB2 Server for VSE and VM does not provide casting between types; for
example, there is no casting between integer and double, char and varchar,
smallint and integer, varchar(18) and varchar(128), and so on.
2. Because the concepts of catalogname to table and schemaname to stored
procedure do not apply to DB2 VSE and VM servers, NULLs are always
returned in these columns of the result sets. If catalogname (for all functions) or
schemaname (for SQLPROCEDURECOL and SQLProcedures) is passed from
the calling program with a value that is not NULL, empty, or ″%″ (for JDBC
only), the stored procedure on a DB2 VSE or VM server will return an empty
result set.
3. SQL procedures always return zeros in the NUM_INPUT_PARM and the
NUM_OUTPUT_PARM column.
282
Database Administration
4. SQL UDTs always return an empty result set, because user-defined types are
not currently supported by DB2 Server for VSE and VM.
5. If the wildcard character % (percent) is used when specifying the input value
for schemaname, procname, and paramname, it should be used at either the
beginning or the end of the string; otherwise, an unexpected result set may be
returned, because the referenced system catalog columns are not defined as
variable length. For more information, refer to the ″LIKE Predicate″ section of
the DB2 Server for VSE & VM SQL Reference manual.
6. The schema stored procedures for CLI/JDBC clients are written in C. C stored
procedures on DB2 Server for VSE and VM cannot handle input parameters
that are more than 254 characters long. For more information, refer to ″Stored
Procedure Parameters″ or ″Using SQL in C″ in the DB2 Server for VSE & VM
Application Programming manual. The input will be truncated. A message is
displayed on the stored procedure server to indicate the last keyword that is
accepted by the stored procedure.
Appendix F. Preparing the Schema Stored Procedures for CLI/ODBC/JDBC/OLE DB Client Applications
283
284
Database Administration
Notices
IBM may not offer the products, services, or features discussed in this document in
all countries. Consult your local IBM representative for information on the
products and services currently available in your area. Any reference to an IBM
product, program, or service is not intended to state or imply that only that IBM
product, program, or service may be used. Any functionally equivalent product,
program, or service that does not infringe any IBM intellectual property right may
be used instead. However, it is the user’s responsibility to evaluate and verify the
operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter
described in this document. The furnishing of this document does not give you
any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10594-1785
U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the IBM
Intellectual Property Department in your country or send inquiries, in writing, to:
IBM World Trade Asia Corporation
Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106, Japan
The following paragraph does not apply to the United Kingdom or any other
country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS
FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore, this statement may not apply
to you.
This information could include technical inaccuracies or typographical errors.
Changes are periodically made to the information herein; these changes will be
incorporated in new editions of the publication. IBM may make improvements
and/or changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those Web
sites. The materials at those Web sites are not part of the materials for this IBM
product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.
285
Licensees of this program who wish to have information about it for the purpose
of enabling: (i) the exchange of information between independently created
programs and other programs (including this one) and (ii) the mutual use of the
information which has been exchanged, should contact:
IBM Corporation
Mail Station P300
522 South Road
Poughkeepsie, NY 12601-5400
U.S.A
Such information may be available, subject to appropriate terms and conditions,
including in some cases, payment of a fee.
The licensed program described in this information and all licensed material
available for it are provided by IBM under terms of the IBM Customer Agreement,
IBM International Program License Agreement, or any equivalent agreement
between us.
Any performance data contained herein was determined in a controlled
environment. Therefore, the results obtained in other operating environments may
vary significantly. Some measurements may have been made on development-level
systems and there is no guarantee that these measurements will be the same on
generally available systems. Furthermore, some measurement may have been
estimated through extrapolation. Actual results may vary. Users of this document
should verify the applicable data for their specific environment.
Information concerning non-IBM products was obtained from the suppliers of
those products, their published announcements, or other publicly available sources.
IBM has not tested those products and cannot confirm the accuracy of
performance, compatibility, or any other claims related to non-IBM products.
Questions on the capabilities of non-IBM products should be addressed to the
suppliers of those products.
All statements regarding IBM’s future direction or intent are subject to change or
withdrawal without notice, and represent goals and objectives only.
This information may contain examples of data and reports used in daily business
operations. To illustrate them as completely as possible, the examples include the
names of individuals, companies, brands, and products. All of these names are
fictitious and any similarity to the names and addresses used by an actual business
enterprise is entirely coincidental.
COPYRIGHT LICENSE:
This information may contain sample application programs in source language,
which illustrates programming techniques on various operating platforms. You
may copy, modify, and distribute these sample programs in any form without
payment to IBM, for the purposes of developing, using, marketing, or distributing
application programs conforming to the application programming interface for the
operating platform for which the sample programs are written. These examples
have not been thoroughly tested under all conditions. IBM, therefore, cannot
guarantee or imply reliability, serviceability, or function of these programs.
286
Database Administration
Trademarks
The following terms are trademarks of International Business Machines
Corporation in the United States, or other countries, or both:
AIX
APL2
AS/400
C/370
CICS
CICS/VSE
DATABASE 2
DataHub
DataPropagator
DB2
DFSMS/VM
DRDA
Distributed Relational Database Architecture
Extended Services
IBM
Information Warehouse
IBMLink
MVS
OS/2
OS/400
QMF
RACF
System/370
SystemView
VM/ESA
VSE/ESA
VTAM
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of
Microsoft Corporation in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks
of others.
Notices
287
|