 |
|
|
|
|
i = institution code, x = user-defined, ... can be > 8 characters
|
| Option | |
|
T) Tutorial |
Information about DB2 |
|
1) DB2I |
DB2 Interactive Functions |
|
1. SPUFI |
SQL Processing Using File Input |
|
2. DCLGEN |
Generate SQL & Source Declarations |
|
3. Program Preparation |
Prepare DB2 Application to Execute |
|
4. Precompile |
Invoke DB2 Precompiler |
|
5. Bind/Rebind/Free |
Perform Plan and Package Functions |
|
6. Run |
Run an SQL Program |
|
7. DB2 Commands |
Issue DB2 Commands |
|
8. Utilities |
Invoke DB2 Utilities |
|
D. DB2I Defaults |
Set Global Parameters |
|
B) BMC Admin |
BMC Administrative Products |
|
1. Alter for DB2 |
Change/migrate DB2 Objects/structures. |
|
2. Catalog Manager |
Execute DDL or query DB2 catalogue. |
|
3. Log Master |
Log Analysis Tool. |
|
I) Insight |
Interactive DB2 Performance Monitor. |
|
R) R-Tools |
DB/Access (Editor) and MOVE |
|
Q) QMF |
QMF Query Management Facility |
1. DB2I - DB2 Interactive Functions
DB2I provides a series of task panels that allow you to perform DB2 programmer, administrator and operator tasks interactively.
1.1 SPUFI (SQL Processor Using File Input)
SPUFI processes SQL statements that are not embedded in a program. It is
especially useful for granting an authorization or creating a table when a host
language is not necessary and for testing statements that are to be embedded in
a program later. You may name a new member of a previously allocated library.
Using the ISPF/PDF editor, you may enter one or more SQL statements to be
processed. Each statement must end with a semicolon. Other choices on the task
panel allow you to process the statement(s), automatically commit (or not) any
changes to the database, and browse the output.
1.2 DCLGEN
Produces data declaration statements for tables for COBOL data structures. The
statements are placed in a copy library and can be included in programs by the
use of INCLUDE statements.
1.3 Program Preparation
Prepares programs to run by guiding you through the preparation tasks in
sequence (compile, assemble, link or run). Programs that run under TSO can be
run by the program preparation panels; however, programs that run under CICS can
be prepared but not executed.
1.4 Precompile
Replaces embedded, coded SQL statements in host language programs with comments
followed by calls to the DB2 language interface. It also creates a data base
request module (DBRM) that describes the SQL statements to the bind process.
1.5 Bind/Rebind/Free
Modifies the relationship between the DB2 plan/package and the associated
application. Bind - Establishes or replaces an application plan/package. Rebind
- Modifies an existing application plan/package. Free - Deletes an application
plan/package.
1.6 Run
Executes programs that will run under the TSO environment.
1.7 DB2 Commands
Allow you to issue up to four lines of DB2 commands. The command recognition
character '-' is required.
1.8 Utilities
Allow you to control the operation of DB2 utility programs, which run separately
from the DB2 processor. Using the task panel, you can create and submit a
utility job, create and edit a utility job, display the STATUS of or STOP a
utility.
| CHECK DATA | Indicates which rows in the table space are to be checked for referential constraints. |
| CHECK INDEX | Tests whether indexes are consistent with the data they index, and issues a warning message if it finds an error. |
| CHECK LOB | Identifies any structural defects in a LOB table space and any invalid LOB values. |
| COPY | Copies data from a table space to a sequential data set. It can be used to copy the entire space or only the data that changed since the last copy was made. |
| DIAGNOSE | Generates information useful in diagnosing problems. |
| LOAD | Loads data from a sequential data set to DB2 tables. |
| MERGECOPY | Merges several partial copies of a table space into one copy and merges partial copies with a full image copy to make a new full image copy. |
| MODIFY | Deletes a row from the SYSIBM.SYSCOPY catalog table and the SYSIBM.SYSLGRNG directory table. |
| QUIESCE | Establishes a point of recovery for a table space, partition, table space set or a list of table spaces and table space sets. |
| REBUILD INDEX | Reconstructs indexes from the table they reference. |
| RECOVER | Recovers data to the current state or to a previous point in time by restoring to an image copy, then applying log records. |
| REORG INDEX | Reorganizes indexes & unloads data in the format acceptable to the LOAD utility. |
| REORG TABLESPACE | Reorganizes table spaces & unloads data in the format acceptable to the LOAD utility. |
| REPAIR | Allows you to change specific values within a DB2 database without using SQL. |
| REPORT | Reports table space information, including recovery history from SYSIBM.SYSCOPY catalog table, log ranges from SYSIBM.SYSLGRNG, volume serial numbers where archive log datasets reside, and name of all table spaces in a table space set. |
| RUNSTATS | Gathers summary information about the characteristics of data in table spaces and indexes. |
| STOSPACE | Updates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes. |
| UNLOAD | Unloads data from one or more table spaces or image copy data sets to one or more sequential datasets in an external format. |
1.D DB2I Defaults
Allows you to change default values that are used for several DB2I tasks. (The
task panel may also be invoked during program preparation).
Q. QMF - Query Management Facility
The Query Management Facility is an interactive query product that allows you to
create reports and charts from relational data under MVS/XA and MVS/ESA. With
QMF, you can:
Access data kept in DB2 tables
Perform calculations on that data
Insert new data and change or delete existing data
Produce and print customized reports for your data
Create and format charts
Communicate with other products
Multi-row Fetch Highlights
- Allows multiple rows to be returned with a single fetch.
- Can improve performance when large amounts of data are being retrieved.
- Data is returned into individual arrays, not structured arrays.
- A ROWSET is the group rows returned with a single multi-row fetch.
- The ROWSET size is specified on the FETCH statement and can be a max size of 32,767.
- The cursor must be declared for multi-row fetching.
Performance Considerations
- We saw approximately 50% CPU time reduction.
- Performance improvements are reduced the smaller the ROWSET and the more columns fetched.
- Performance gains start at a ROWSET of 10 and the best performance increase has been shown to be with a ROWSET of 100.
New SQL statement GET DIAGNOSTICS
- Use the GET DIAGNOSTICS with Multi-row fetch to determine the acutal number of rows returned.
- GET DIAGNOSTICS provides diagnostic information for a entire SQL statement or for multiple conditions.
- With a Multi-row fetch there is the possibility of having an error for each row of a ROWSET.
Multi-row Insert is a similar new feature that can also be used to improve CPU performance.
Additional information can be found in the DB2 UDB for z/OS V8 Application Programming and SQL Guide and the DB2 UDB for z/OS V8 SQL Reference.
Below is a sample of COBOL code using the Multi-row fetch and GET DIAGNOSTICS:
.
.
.
01 AORDERS.
10 AORDERS-ORDER-ID OCCURS 500 TIMES PIC S9(5)V
USAGE COMP-3.
10 AORDERS-CUST-ID OCCURS 500 TIMES PIC X(5).
10 AORDERS-ORDER-DATE OCCURS 500 TIMES PIC X(10).
10 AORDERS-ORDER-TIME OCCURS 500 TIMES PIC X(8).
10 AORDERS-FREIGHT-CHARGES OCCURS 500 TIMES PIC S9(2)V9(2)
USAGE COMP-3.
10 AORDERS-ORDER-SALESMAN OCCURS 500 TIMES PIC X(6).
10 AORDERS-ORDER-POSTED-DATE OCCURS 500 TIMES PIC X(26).
10 AORDERS-ORDER-SHIP-DATE OCCURS 500 TIMES PIC X(8).
******************************************************************
* INDICATOR VARIABLE STRUCTURE *
******************************************************************
01 IORDERS.
10 IORDERS-FREIGHT-CHARGES OCCURS 500 TIMES PIC S9(4)
USAGE COMP.
10 IORDERS-ORDER-SALESMAN OCCURS 500 TIMES PIC S9(4)
USAGE COMP.
.
.
.
EXEC
SQL
DECLARE C1
CURSOR
WITH ROWSET
POSITIONING FOR
SELECT ORDER_ID, CUST_ID,
ORDER_DATE, ORDER_TIME,
FREIGHT_CHARGES, ORDER_SALESMAN,
ORDER_POSTED_DATE, ORDER_SHIP_DATE
FROM
PSTDEMO.ORDERS
ORDER BY
ORDER_ID
END-EXEC.
.
.
.
EXEC
SQL
OPEN C1
END-EXEC
IF
SQLCODE NOT EQUAL 0
MOVE 'O1 ERROR OPENING CURSOR C1' TO
WS-MSG
GO TO 9998-SQL-ERROR
END-IF
PERFORM 1000-MULTI-ROW-FETCHING
UNTIL SQL-EOF = 'Y'
.
.
.
1000-MULTI-ROW-FETCHING.
EXEC
SQL
FETCH NEXT ROWSET FROM C1
FOR :ROWSET-SIZE ROWS
INTO :AORDERS-ORDER-ID, :AORDERS-CUST-ID,
:AORDERS-ORDER-DATE,
:AORDERS-ORDER-TIME,
:AORDERS-FREIGHT-CHARGES:IORDERS-FREIGHT-CHARGES,
:AORDERS-ORDER-SALESMAN:IORDERS-ORDER-SALESMAN,
:AORDERS-ORDER-POSTED-DATE,
:AORDERS-ORDER-SHIP-DATE
END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN 100
MOVE 'Y' TO
SQL-EOF
WHEN OTHER
MOVE '02
ERROR FETCHING CURSOR C1' TO WS-MSG
GO TO
9999-GET-DIAGNOSTICS
END-EVALUATE
EXEC
SQL
GET DIAGNOSTICS
:ROW-COUNT =
ROW_COUNT
END-EXEC
PERFORM VARYING SS FROM 1 BY 1
UNTIL SS > ROW-COUNT
.
.
.
END-PERFORM.
.
.
.
9998-SQL-ERROR.
MOVE SQLCODE TO WS-SQLCODE
STRING 'SQLCODE = ' WS-SQLCODE ' ' WS-MSG
DELIMITED BY SIZE INTO WS-OUT-MSG
DISPLAY WS-OUT-MSG
STRING 'SQLERRMC = ' SQLERRMC
DELIMITED BY SIZE INTO WS-OUT-MSG
DISPLAY WS-OUT-MSG
STOP RUN.
9999-GET-DIAGNOSTICS.
DISPLAY WS-MSG
EXEC
SQL
GET DIAGNOSTICS
:COND-COUNT =
NUMBER
END-EXEC
PERFORM VARYING SS FROM 1 BY 1
UNTIL SS > COND-COUNT
EXEC
SQL
GET DIAGNOSTICS CONDITION :SS
:SQL-CODE =
DB2_RETURNED_SQLCODE,
:SQL-STATE =
RETURNED_SQLSTATE,
:ROW-NUM =
DB2_ROW_NUMBER,
:TOKEN-COUNT
= DB2_TOKEN_COUNT,
:MSG-ID =
DB2_MESSAGE_ID,
:MSG-TEXT = MESSAGE_TEXT
END-EXEC
MOVE
SQL-CODE TO WS-SQLCODE
DISPLAY 'SQL-CODE = ' WS-SQLCODE
DISPLAY 'SQL-STATE = ' SQL-STATE
DISPLAY 'ROW-NUM = ' ROW-NUM
DISPLAY 'TOKEN-COUNT = ' TOKEN-COUNT
DISPLAY 'MSG-ID = ' MSG-ID
MOVE MSG-TEXT(1:60) TO WS-MSG
DISPLAY 'MSG-TEXT = ' WS-MSG
MOVE MSG-TEXT(61:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(121:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(181:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(241:60) TO WS-MSG
DISPLAY ' ' WS-MSG
END-PERFORM
STOP
RUN.
|
Copyright © 2006
NWRDC All Right Reserved |
|