DB2 Tutorial

                                       About Us          Services          Courses          Case Studies          Mainframe Forum          Mainframe Jobs          Contact Us           Mainframe Connectivity


RDBMS Concepts:

Relational properties: Relation is a mathematical term for a table.

Hence Relational database is perceived by user as a set of tables.

The main characteristics of relation are:

All data values are atomic

Entries in column are from the domain

Sequence of rows (top to bottom) is insignificant

Each row is unique

Sequence of columns (left to right) is insignificant

Relational Terminology:

Relation

A table

Tuple or Row

Row contains an entry for each attribute

Attribute or column

Columns or the characteristics that define the entity

Domain

A range of values ( or pool)

Entity

Some object about which we wish to store information

Null value

Represents an unknown value

Atomic

Smallest unit of data, the individual data value

Candidate key

Some attribute (or set of attributes) that may uniquely identify each row (tuple) in the relation(table). This exists only for a short period of time and the primary and attribute key take its place

Primary key

The candidate key that is chosen for primary attributes to uniquely identify each row

Alternate key

The remaining candidate keys that were not chosen as primary key

Foreign key

An attribute of one relation that might be a primary key of another ralation

Normalization:

Normalization is done to bring the design of database to a standardized model.

1NF: All entities must have a unique identifier, or key, that can be

composed of one or more attributes.

All attributes must be atomic and non-repeating.

2NF: Partial functional dependencies removed – attributes that are not a part of

the key must depend on the entire key for that entity.

3NF: Transitive dependencies removed – attributes that are not a part of the

key must not depend on any non-key attribute.

4NF: Multi valued dependencies removed

5NF: Remaining anomalies removed

Types of Integrity:

Entity Integrity:

Rule states that no column that is part of a primary key can have a null value.

Referential Integrity:

Rule states that every foreign key in the first table must either match a primary

key value in the second table or must be wholly NULL.

Domain Integrity:

Integrity of information allowed in column

DB2 Objects and Data types:

The complex DB2 internal structure has been divided into 5 major components / Address Spaces:

1. System Services component / System Services Address Space (Job: DSN1MSTR)

2. Locking Services component/ Locking Services Address Space (Job: IRLMPROC)

3. Database Services component/ Database Services  Address Space (DSN1DBM1)

4. Distributed Data facility component( DDF ) / Distributed Data Address Space (Job: DSN1DIST)

5. Stored Procedure Services Component ( DSN1SPAS )

System Services component:

Controlling connections to other MVS subsystems like CICS, IMS/DC, TSO etc.

Handles system start-up, shutdown and operator communication.Managing the system log, 
which records the information necessary for recovering user and system data

in case of system failure. When the active log dataset becomes full the system

shifts to a new dataset and copies the old data to archive log.

Information regarding the log datasets is recorded on a system dataset called the bootstrap dataset.

Locking Services component:

Provides necessary controls for managing concurrent access to data using the IRLM.

IRLM-IMS resource manager is an MVS system and a general-purpose lock manager

that aids in maintaining data integrity.

Database Services component:

Supports the definition, retrieval and update of DB2 data using a series of six sub components.

Precompiler:

This is a pre-processor for host programming language. Produces a DBRM.

Bind:

Binds a given DBRM to produce what is know as Plan. Bind has two main functions

Syntax checking and optimized access path creation (plan generation)

Runtime Supervisor:

When the program executes first time the control goes to the runtime supervisor.

It oversees execution and makes requests to the Data manager.

Database Manager:

Does all the operations such as the search, retrieval, update index, maintenance etc.

It also manages the databases. It invokes the system components as necessary in order to

perform detailed functions such as locking, logging, I-O operations etc.

Buffer Manager:

This is the component responsible for the physical transfer of data between external storage

and virtual memory. It uses techniques such as read-ahead buffering and look

–aside buffering to get the best performance of the buffer pools and to

minimize the amount of physical I/O required.

UTILITIES:

The database services component also maintains certain control and descriptor

information regarding the database tables and their columns, database backup operations.

This information is divided into two groups, which is namely catalog and the directory.

The catalog consists of regular tables and is accessible by means of SQL statements.

The directory cannot be accessed by SQL statements and it is for the internal use of the DB2.

Both contain the same information

 

Distributed Data facility component:

Distributed DB2 requests are carried out through DDF

Enables database access by remote systems

 

DB2 objects:-

There are two types of elements or objects in DB2.

System Objects: Objects that are controlled and used by DB2.

Example: DB2 Directory, DB2 catalog, Active & Archive logs, Boot Strap data set (BSDS),

Buffer pools, catalog visibility database and locks.

Data objects:- Objects that are created and used by the users.

Example: Tables, Indexes, Views, Table spaces, Databases, Storage groups.

Storage Groups:-

Storage group is a collection of direct access volumes, all of the same type.

DB2 objects are allocated in storage group. Max no of volumes per storage group

are 133(ideally 3 or 4) and all volumes must be of same device type – 3380, 3390 etc.

Default STOGROUP is SYSDEFLT. It is created when DB2 is installed.

The underlying VSAM datasets are created and maintained by DB2.

When a given space needs to be extended, storage is acquired from the appropriate STOGROUP

DB2 uses VSAM LDS dataset to represent DB2 table spaces and index spaces

Bufferpools :-

BUFERPOOL is buffer area used to maintain recently accessed table and index pages.

Data is first read from a table, which is in a VSAM dataset and is moved into a

BUFFERPOOL and from there it is sent to the requester.

There are totally 80 BUFFERPOOL options –

50 4K BUFFERPOOLS(BP0 thru BP49)
and 10 8K BUFFERPOOLS – BP8K0 thru BP8K9.
and 10 16K BUFFERPOOLS – BP16K0 thru BP16K9.
and 10 32K BUFFERPOOLS – BP32K0 thru BP32K9.

DB2 Object - Alias & Synonyms:

Alias and Synonym are alternate name for a table.

The main differences between them are listed below:

 

SYNONYM

ALIAS

An alternate private name for a Table or a View

An alternate private name for a Table or a View

It can be used only by its creator

It can be used by users other than its creator

It can’t refer to a remote table

It can refer to a remote table

When a Base Table/View is dropped, all Synonyms defined on it are also will be dropped

When a Base Table/View is dropped, all aliases defined on it are NOT dropped

 

Alias is created for a remote table, thereby giving it a shorter, local name because it no longer requires the location prefix

Introduction to SQL:

Structured Query Language is a powerful language that performs the functions

of data manipulation(DML), data definition(DDL) and data control or data authorization(DAL/DCL).

SQL is a set level language and a non-procedure language.

Where as programming language like COBOL, PL/I are row level languages.

And it is used by many Relational database products like DB2, Oracle, dbase and Sybase etc.

SQL can be used as an interactive query language as well as a programming language.

Based on the type of usage, SQL can be classified into 2 categories

Static SQL: It is embedded in an application program written

in a high-level language like COBOL, PL/I.

SQL statements are hard-coded in the application program

They can’t be modified during the program’s execution except for changes

to the values assigned to the host variables

Cursors will be used to access set-level data

The general syntax will be like:

      EXEC SQL

           SQL statement

      END-EXEC.

Dynamic SQL: It could be embedded in an application program where

it will be constructed at run-time. Or it could be executed at a terminal.

Statements can be changed throughout the program’s execution

When the SQL is bound, the application plan or package that is created does not

contain the same information as that for a static SQL program

The access paths cannot be determined before execution

Embedded SQL: For an application program to access DB2 data,

the SQL statements have to be embedded in the program statements of the

high level language like COBOL, PL/I etc.

How to use SQL to work with a DB2 database:

SQL Statements can be vided in to 3 categories:

The data definition language (DDL)

The data manipulation language (DML)

The data control language (DCL)

Application programmers typically work with DML statements,

while database administrators (DBA’s) use the DDL and DCL statements.

SQL statements for data definition

Statement

Description

CREATE/ALTER/DROP

DATABASE

Creates, alters, or drops a database

CREATE/ALTER/DROP

STOGROUP

Creates, alters, or drops a storage group

CREATE/ALTER/DROP

TABLESPACE

Creates, alters, or drops a tablespace in the specified database

CREATE/ALTER/DROP

TABLE

Creates, alters, or drops a table in the specified database

CREATE/ALTER/DROP

INDEX

Creates, alters, or drops an index for the specified table

CREATE/DROP

VIEW

Creates or drops a view of the specified database

CREATE/DROP

ALIAS

Creates or drops an alias of a table or a view that may or may not be on the current server

CREATE/DROP

SYNONYM

Creates or drops a synonym of a table or a view that must be on the current server

SQL statements for data manipulation

Statement

Description

SELECT

Receives data from one or more tables

INSERT

Inserts one or more new rows into a table

UPDATE

Updates one or more rows form a table

DELETE

Deletes one or more rows from a table

OPEN

Opens a cursor

FETCH

Positions a cursor on the next row of its result table and assigns the values of that row to host variables

CLOSE

Closes a cursor

SQL statements for data control

Statement

Description

GRANT

Granting privileges is done by means of the GRANT statement.

REVOKE

Revoking privileges is done means of the REVOKE statement.

 
 
CREATE TABLE SAMPLE
(COL_ONE        SMALLINT        NOT NULL,
COL_TWO         INTEGER         NOT NULL,
COL_THREE       DECIMAL(7,2)    NOT NULL,
COL_FOUR        FLOAT(21)       NOT NULL,
COL_FIVE        FLOAT(35)       NOT NULL,
COL_SIX         CHAR(10)        NOT NULL,
COL_SEVEN       VARCHAR(20)     NOT NULL,
COL_EIGHT       GRAPHIC(10)     NOT NULL,
COL_NINE        VARGRAPHIC(100) NOT NULL,
COL_TEN         DATE            NOT NULL,
COL_ELEVEN      TIME            NOT NULL,
COL_TWELEVE     TIMESTAMP       NOT NULL)
IN INDDB.INDTS
 
 
DCLGEN COPY BOOK FOR THE ABOVE SAMPLE TABLE  
                                                                
    EXEC SQL DECLARE TUTORIAL.SAMPLE TABLE                       
   (COL_ONE       SMALLINT          NOT NULL,                  
    COL_TWO       INT               NOT NULL,                  
    COL_THREE     DECIMAL(7, 2)     NOT NULL,                  
    COL_FOUR      FLOAT(4)          NOT NULL,                  
    COL_FIVE      FLOAT             NOT NULL,                  
    COL_SIX       CHAR(10)          NOT NULL,                  
    COL_SEVEN     VARCHAR(20)       NOT NULL,                  
    COL_EIGHT     GRAPHIC(10)       NOT NULL,                  
    COL_NINE      VARGRAPHIC(100)   NOT NULL,                  
    COL_TEN       DATE              NOT NULL,                  
    COL_ELEVEN    TIME              NOT NULL,                  
    COL_TWELEVE   TIMESTAMP     NOT NULL)                   
    END-EXEC.                                                    
                                                                
01  SAMPLE-REC.                                                  
    03 HS-COL-ONE                     PIC S9(4) COMP.            
    03 HS-COL-TWO                     PIC S9(9) COMP.            
    03 HS-COL-THREE                    PIC S9(5)V9(2) COMP-3.     
    03 HS-COL-FOUR                     COMP-1.                    
    03 HS-COL-FIVE                     COMP-2.                    
    03 HS-COL-SIX                      PIC X(10).                 
    03 HS-COL-SEVEN.                                             
       49 HS-COL-SEVEN-LEN            PIC S9(4) COMP.            
       49 HS-COL-SEVEN-TEXT           PIC X(20).                 
    03 HS-COL-EIGHT                    PIC N(10).                 
    03 HS-COL-NINE.                                              
       49 HS-COL-NINE-LEN             PIC S9(4) COMP.            
       49 HS-COL-NINE-TEXT            PIC N(100).                
    03 HS-COL-TEN                      PIC X(10).                 
    03 HS-COL-ELEVEN                   PIC X(8).                  
    03 HS-COL-TWELEVE                 PIC X(26).  
 
                                                                                                                                                                             http://www.mainframesguru.com
DB2 data structures
-------------------
1. Storage Groups
2. Databases
3. Table Spaces
4. Tables
5. Index Spaces
6. Indexes
7. Synonyms
8. Aliases
9. Views
10. Keys
11. Schemas and schema qualifiers
 
Storage Groups:
---------------
DB2 storage groups are a set of volumes on disks that hold the data sets
in which tables and indexes are actually stored.
CREATE STOGROUP:
----------------
The CREATESG privilege
SYSADM or SYSCTRL authority
 
CREATE STOGROUP MANNSTG  
       VOLUMES (OZAV06,OZAV17)
       VCAT OZADB2;
 
The default storage group, SYSDEFLT, is created when you install DB2.
The description of a storage group names the group and identifies its volumes
and the VSAM (virtual storage access method) catalog that records the data sets.

ALTER STOGROUP:
---------------
Ownership of the storage group
SYSADM or SYSCTRL authority
   ALTER STOGROUP DSN8G910
       ADD VOLUMES (DSNV04,DSNV05);
   ALTER STOGROUP DSN8G910
     REMOVE VOLUMES (DSNV04,DSNV05);

ALTER TABLE:
-----------
The ALTER privilege on the table
Ownership of the table
DBADM authority for the database
SYSADM or SYSCTRL authority

   ALTER TABLE DSN8910.DEPT
     ALTER COLUMN DEPTNAME SET DATA TYPE VARCHAR(50)
     ADD BLDG CHAR(3) FOR SBCS DATA;
  
   ALTER TABLE DSN8910.EMP
     VALIDPROC DSN8EAEM;
  
   ALTER TABLE DSN8910.EMP
     VALIDPROC NULL;
  
   ALTER TABLE DSN8910.DEPT
     FOREIGN KEY(ADMRDEPT) REFERENCES DSN8910.DEPT ON DELETE CASCADE;
  
   ALTER TABLE DSN8910.EMP
     ADD CHECK (SALARY >= 10000);
  
   ALTER TABLE PRODINFO
     FOREIGN KEY (PRODNAME,PRODVERNO)
       REFERENCES PRODVER_1 (VERNAME,RELNO) ON DELETE RESTRICT;
  
   ALTER TABLE DSN8910.DEPT
     ADD CONSTRAINT KEY_DEPTNAME UNIQUE( DEPTNAME );
  
   ALTER TABLE TRANSCOUNT ADD MATERIALIZED QUERY
     (SELECT ACCTID, LOCID, YEAR, COUNT(*) as cnt
      FROM TRANS
      GROUP BY ACCTID, LOCID, YEAR )
      DATA INITIALLY DEFERRED
      REFRESH DEFERRED
      MAINTAINED BY USER;

DROP:
-----
Ownership of the object (for an index, the owner is the owner of the table or index)
DBADM authority
SYSADM or SYSCTRL authority
 
DROP ALIAS
DROP DATABASE
DROP FUNCTION
DROP INDEX
DROP PACKAGE
DROP PROCEDURE
DROP ROLE
DROP SEQUENCE
DROP STOGROUP
DROP SYNONYM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP TYPE
DROP VIEW
Database
--------
CREATE DATABASE:
Authorization
The CREATEDBA privilege
The CREATEDBC privilege
SYSADM or SYSCTRL authority
 
CREATE DATABASE EFUNDB
       STOGROUP EFUNSTG
       BUFFERPOOL BP8K1 
       INDEXBP BP2;
 
CREATE DATABAS DSN8TEMP
       CCSID ASCII;
 
ALTER DATABASE:
The DROP privilege on the database
Ownership of the database
DBADM or DBCTRL authority for the database
SYSADM or SYSCTRL authority
 
   ALTER DATABASE ABCDE
     BUFFERPOOL BP2
     INDEXBP BP2;
 
Table space:
------------
CREATE TABLESPACE:
The CREATETS privilege for the database
DBADM, DBCTRL, or DBMAINT authority for the database
SYSADM or SYSCTRL authority
 
 
ALTER TABLESPACE:
 
   ALTER TABLESPACE DSN8D91A.DSN8S91D
     BUFFERPOOL BP2
     LOCKSIZE PAGE;
 
   ALTER TABLESPACE DSN8D91A.DSN8S91E
     CLOSE NO
     SECQTY -1
     ALTER PARTITION 1 PCTFREE 20;
 
   ALTER TABLESPACE TS01DB.TS01TS
     MAXPARTITIONS 30;

NAME     TYPE PART STATUS      
-------- ---- ---- -------------
PARTPRTS TS    001 RW,COPY     
PARTPRTS TS    002 RW,COPY     
PARTPRTS TS    003 RW,COPY     
SHIPPRTS TS    001 RW,CHKP,COPY
SHIPPRTS TS    002 RW,CHKP,COPY
SHIPPRTS TS    003 RW,CHKP,COPY
SUPPPRTS TS    001 RW,COPY     
SUPPPRTS TS    002 RW,COPY     
SUPPPRTS TS    003 RW,COPY     
PARTIX   IX    001 RW,ICOPY    
PARTIX   IX    002 RW,ICOPY    
PARTIX   IX    003 RW,ICOPY    
SHIPIX   IX    001 RW,ICOPY    
SHIPIX   IX    002 RW,ICOPY    
SHIPIX   IX    003 RW,ICOPY    
SUPPIX   IX    001 RW,RBDP,ICOPY
SUPPIX   IX    002 RW,RBDP,ICOPY
SUPPIX   IX    003 RW,RBDP,ICOPY
Modify Utility
--------------
SELECT * FROM SYSIBM.SYSCOPY
  WHERE DBNAME = 'EFUNDB'   
 AND TSNAME = 'SUPPPRTS';   
-------------------------
OZA058.MANN.CTRLLIB(MODIFY)
------------------------------------------
OZA058.MANN.CTRLLIB(QUCECRD)
SELECT HEX(START_RBA) FROM SYSIBM.SYSCOPY;
OZA058.MANN.CTRLLIB(RCVRRBA)
OZA058.MANN.CTRLLIB(MODIFY)
SELECT HEX(START_RBA) FROM SYSIBM.SYSCOPY;
Version 7 Utilities
--------------------
CHECK DATA,
CHECK INDEX,
CHECK LOB,   
COPY,
DIAGNOSE,
LOAD,
MERGE,
MODIFY,  
QUIESCE,
REBUILD,
RECOVER,
REORG INDEX,
REORG LOB,
REORG TABLESPACE,
REPORT,  
REPAIR,
RUNSTATS,
STOSPACE,
UNLOAD

1. UNLOAD Utility
-----------------
OZA058.MANN.CTRLLIB(UNLOAD)
OZA058.MANN.UNLOAD.TST01
OZA058.MANN.UNLOAD.PUNCH.TST01
OZA058.MANN.UNLOAD.SUPP.TST01
OZA058.MANN.UNLOAD.PUNCH.SUPP.TST01
OZA058.MANN.UNLOAD.PART.TST01
OZA058.MANN.UNLOAD.PUNCH.PART.TST01
OZA058.MANN.UNLOAD.SHIP.TST01
OZA058.MANN.UNLOAD.PUNCH.SHIP.TST01

copy backup datasets
--------------------
OZA058.MANN.COPY.SUPPPRTS.TST02

db2 commands
-------------
-DIS DB(DBRAJU) SPACENAM(*)             
-DISPLAY DB(EFUNDB)                     
-DISPLAY DB(DBDBA) LIMIT(*)             
-ALTER BUFFERPOOL(BP1) VPSIZE(2000)     
-DISPLAY LOG                            
-REPAIR DATABASE(DB1) SPACENAM(YSTS) COPY
 

CATALOG TABLES : 108  in db2 ver 9.0
------------------------------------
Volumes
-------
SYSIBM.SYSVOLUMES

Storage Groups
--------------
SYSIBM.SYSSTOGROUP

Database:
---------
SYSIBM.SYSDATABASE
SYSIBM.SYSDBAUTH
 
Table Spaces:
-------------
SYSIBM.SYSTABLESPACE
SYSIBM.SYSTABLESPACESTATS
SYSIBM.SYSTABLEPART
SYSIBM.SYSTABLEPART_HIST

Tables:
-------
SYSIBM.SYSTABLES
SYSIBM.SYSTABLES_HIST
SYSIBM.SYSTABSTATS
SYSIBM.SYSTABSTATS_HIST
SYSIBM.SYSTABAUTH
SYSIBM.SYSTABCONST

Synonyms & Alias
----------------
SYSIBM.SYSSYNONYMS

Views:
------
SYSIBM.SYSVIEWS
SYSIBM.SYSVIEWDEP

Columns
-------
SYSIBM.SYSCOLUMNS
SYSIBM.SYSCOLUMNS_HIST
SYSIBM.SYSCOLAUTH
SYSIBM.SYSCOLDIST
SYSIBM.SYSCOLDISTSTATS
SYSIBM.SYSCOLDIST_HIST
SYSIBM.SYSCOLSTATS
Data Types
----------
SYSIBM.SYSDATATYPES

Foreign Keys:
-------------
SYSIBM.SYSFOREIGNKEYS
DBRM'S
------
SYSIBM.SYSDBRM
Utilities Table
---------------
SYSIBM.SYSCOPY
Plan
----
SYSIBM.SYSPLAN
SYSIBM.SYSPLANAUTH
SYSIBM.SYSPLANDEP
Package
-------
SYSIBM.SYSPACKAGE
SYSIBM.SYSPACKAUTH
SYSIBM.SYSPACKDEP
SYSIBM.SYSPACKLIST
SYSIBM.SYSPACKSTMT
Triggers
--------
SYSIBM.SYSTRIGGERS
Keys
-----
SYSIBM.SYSKEYS
------------  indexes ...
SYSIBM.SYSINDEXES
SYSIBM.SYSINDEXES_HIST
SYSIBM.SYSINDEXPART
SYSIBM.SYSINDEXPART_HIST
SYSIBM.SYSINDEXSPACESTATS  --   not in ver 7
SYSIBM.SYSINDEXSTATS
SYSIBM.SYSINDEXSTATS_HIST
--------------------------
SYSIBM.IPLIST
SYSIBM.IPNAMES
SYSIBM.LOCATIONS
SYSIBM.LULIST
SYSIBM.LUMODES
SYSIBM.LUNAMES
SYSIBM.MODESELECT
---------------------------
Routines
--------
SYSIBM.SYSROUTINEAUTH
SYSIBM.SYSROUTINES
SYSIBM.SYSROUTINESTEXT
SYSIBM.SYSROUTINES_OPTS
SYSIBM.SYSROUTINES_SRC
Schemas
-------
SYSIBM.SYSSCHEMAAUTH
Sequence
--------
SYSIBM.SYSSEQUENCEAUTH
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSEQUENCESDEP
Java related Tables
-------------------
SYSIBM.SYSJARCLASS_SOURCE
SYSIBM.SYSJARCONTENTS
SYSIBM.SYSJARDATA
SYSIBM.SYSJAROBJECTS
SYSIBM.SYSJAVAOPTS
SYSIBM.SYSJAVAPATHS

-------------------
SYSIBM.SYSAUXRELS
SYSIBM.SYSCHECKDEP
SYSIBM.SYSCHECKS
SYSIBM.SYSCHECKS2
SYSIBM.SYSCONSTDEP
SYSIBM.SYSCONTEXT
SYSIBM.SYSCONTEXTAUTHIDS
SYSIBM.SYSCTXTTRUSTATTRS
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSDUMMY1
SYSIBM.SYSENVIRONMENT
SYSIBM.SYSFIELDS
SYSIBM.SYSKEYCOLUSE

SYSIBM.SYSKEYTARGETS         -  not in ver 7
SYSIBM.SYSKEYTARGETSTATS
SYSIBM.SYSKEYTARGETS_HIST
SYSIBM.SYSKEYTGTDIST
SYSIBM.SYSKEYTGTDISTSTATS
SYSIBM.SYSKEYTGTDIST_HIST
SYSIBM.SYSLOBSTATS
SYSIBM.SYSLOBSTATS_HIST
SYSIBM.SYSOBJROLEDEP
SYSIBM.SYSPARMS
SYSIBM.SYSPKSYSTEM
SYSIBM.SYSPLSYSTEM
SYSIBM.SYSRELS
SYSIBM.SYSRESAUTH
SYSIBM.SYSROLES  -- not in ver 7

SYSIBM.SYSSTMT
SYSIBM.SYSSTRINGS
SYSIBM.SYSUSERAUTH
SYSIBM.SYSXMLRELS
SYSIBM.SYSXMLSTRINGS
SYSIBM.USERNAMES
SYSIBM.XSRCOMPONENT
SYSIBM.XSROBJECTS
SYSIBM.XSROBJECTCOMPONENTS
SYSIBM.XSROBJECTGRAMMAR
SYSIBM.XSROBJECTHIERARCHIES
SYSIBM.XSROBJECTPROPERTY
SYSIBM.XSRPROPERTY
-------------------------
VOLUMES
-------
OZAV06
OZAV12
OZAV16
OZAV17
OZAV17
OZAV04
OZAV07
OZAV17
OZAV05

vcatname.DSNDBD.dbname.tsname.I0001.Annn

OZADB2.DSNDBC.EFUNDB.PARTPRTS.I0001.A001
OZADB2.DSNDBC.EFUNDB.PARTPRTS.I0001.A002
OZADB2.DSNDBC.EFUNDB.PARTPRTS.I0001.A003
OZADB2.DSNDBC.EFUNDB.SHIPPRTS.I0001.A001
OZADB2.DSNDBC.EFUNDB.SHIPPRTS.I0001.A002
OZADB2.DSNDBC.EFUNDB.SHIPPRTS.I0001.A003
OZADB2.DSNDBC.EFUNDB.SUPPPRTS.I0001.A001
OZADB2.DSNDBC.EFUNDB.SUPPPRTS.I0001.A002
OZADB2.DSNDBC.EFUNDB.SUPPPRTS.I0001.A003
--------------------
OZA059.XANSA.SOURCE
OZA059.XANSA.SPUFI
OZA059.XANSA.DBUTLS
OZA059.XANSA.JCLUTLS
OZA059.XANSA.PROCLIB
OZA059.XANSA.JCLLIB
OZA059.XANSA.COPYLIB
OZA059.XANSA.CTRLRIB
--------------------
Create Storage Group Syntax:
----------------------------
CREATE STOGROUP MANNSTG  
       VOLUMES (OZAV06,OZAV17)
       VCAT OZADB2;       

CREATE STOGROUP EFUNSTG         
       VOLUMES (OZAV06,OZAV17) 
       VCAT OZADB2;            
SYSIBM.SYSSTOGROUP
SELECT * FROM SYSIBM.SYSSTOGROUP
WHERE CREATOR = 'OZA058';       
Create Database Syntax:
-----------------------
CREATE DATABASE EFUNDB
       STOGROUP EFUNSTG
       BUFFERPOOL BP2 
       INDEXBP BP2;   
SELECT * FROM SYSIBM.SYSDATABASE
WHERE CREATOR = 'OZA058';       
Create Partitioned Table Space:
-------------------------------

CREATE TABLESPACE SUPPPRTS         
       IN MANNDB                   
      NUMPARTS 3                   
      (PART 1 USING STOGROUP MANNSTG
       PRIQTY 200                  
       SECQTY 50                   
       FREEPAGE 5                  
       PCTFREE 10,                 
      PART 2 USING STOGROUP MANNSTG
       PRIQTY 200                  
       SECQTY 50                   
       FREEPAGE 5                  
       PCTFREE 10,                 
      PART 3 USING STOGROUP MANNSTG
       PRIQTY 200                  
       SECQTY 50       
       FREEPAGE 5      
       PCTFREE 10      
      )                
       BUFFERPOOL BP12 
       MAXROWS 200     
        CLOSE YES      

CREATE TABLESPACE PARTPRTS          
        IN MANNDB                   
       NUMPARTS 3                   
       (PART 1 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5                  
        PCTFREE 10,                 
       PART 2 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5                  
        PCTFREE 10,                 
       PART 3 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5     
        PCTFREE 10     
       )               
        BUFFERPOOL BP12
        MAXROWS 200    
        CLOSE YES      
 
CREATE TABLESPACE SHIPPRTS          
        IN MANNDB                   
       NUMPARTS 3                   
       (PART 1 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5                  
        PCTFREE 10,                 
       PART 2 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5                  
        PCTFREE 10,                 
       PART 3 USING STOGROUP MANNSTG
        PRIQTY 200                  
        SECQTY 50                   
        FREEPAGE 5     
        PCTFREE 10     
       )               
        BUFFERPOOL BP12
        MAXROWS 200    
         CLOSE YES     
 
Creating Segmented Table space:
-------------------------------

CREATE TABLESPACE MANNSETS          
       IN MANNDB              
       USING STOGROUP MANNSTG  
       PRIQTY 200             
       SECQTY 50              
       ERASE NO               
       FREEPAGE 0             
       PCTFREE 10             
       COMPRESS NO            
       SEGSIZE 08              
       BUFFERPOOL BP13        
       LOCKSIZE ANY           
       LOCKMAX SYSTEM         
       CLOSE YES   
       CCSID EBCDIC
       MAXROWS 255 

CREATE TABLESPACE MANNSETS   
       IN MANNDB             
       USING STOGROUP MANNSTG
       PRIQTY 200            
       SECQTY 100            
       SEGSIZE 08            
       LOCKSIZE ANY          
       BUFFERPOOL BP1        
       CLOSE NO;             

Creating Simple Table space:
----------------------------
INDUSAMP -- ASCII
INDUSAM2
INDU_SAMPLE
CREATE TABLESPACE MANNSITS
       IN MANNDB           
       USING STOGROUP MANNSTG
       PRIQTY 200          
       SECQTY 50           
       ERASE NO            
       FREEPAGE 0          
       PCTFREE 10          
       COMPRESS NO         
       BUFFERPOOL BP13     
       LOCKSIZE ANY        
       LOCKMAX SYSTEM      
       CLOSE YES           
CREATE TABLESPACE INDUSITS    
       IN INDUDB              
       USING STOGROUP INDUSTG 
       PRIQTY 200             
       SECQTY 50              
       ERASE NO               
       FREEPAGE 0             
       PCTFREE 10             
       COMPRESS YES           
       BUFFERPOOL BP13        
       LOCKSIZE ANY           
       LOCKMAX SYSTEM      
       CLOSE YES              
       CCSID UNICODE         
       MAXROWS 255                       
-----------------------------------------
CREATE DATABASE TUCDB   
       STOGROUP MANNSTG 
       BUFFERPOOL BP2   
       INDEXBP BP2;     
           
GRANT CREATETAB ON DATABASE TUCDB TO OZA066;
GRANT USE OF TABLESPACE TUCDB.TUCTS TO OZA066;

Creating Tables for Partition Table space
-----------------------------------------

  CREATE TABLE MANN_PART                         
   (PART_NO         CHAR(6)  NOT NULL,            
    PART_NAME       CHAR(20) NOT NULL,            
    PART_COLOR      CHAR(6)  NOT NULL,            
    PART_WEIGHT     SMALLINT NOT NULL,            
    PART_CITY       CHAR(15) NOT NULL,            
    PRIMARY KEY (PART_NO) )                       
    IN MANNDB.PARTPRTS;                           

CREATE UNIQUE INDEX PARTIX ON MANN_PART(PART_NO)
        USING STOGROUP MANNSTG                 
        PRIQTY 12                              
        SECQTY 100                             
        CLUSTER                                
        (PART 1 VALUES ('P100'),               
         PART 2  VALUES ('P200'),              
         PART 3  VALUES ('P300'))              
        BUFFERPOOL BP0                                          
        COPY YES                                                
        CLOSE NO;                                               
  INSERT INTO MANN_PART VALUES ('P1','NUT','RED',12,'LONDON');    
  INSERT INTO MANN_PART VALUES ('P2','BOLT','GREEN',17,'PARIS');  
  INSERT INTO MANN_PART VALUES ('P3','SCREW','BLUE',17,'ROME');   
  INSERT INTO MANN_PART VALUES ('P4','SCREW','RED',14,'LONDON');  
  INSERT INTO MANN_PART VALUES ('P5','CAM','BLUE',12,'PARIS');    
  INSERT INTO MANN_PART VALUES ('P6','COG','RED',19,'LONDON');    
                                                                  
  INSERT INTO MANN_PART VALUES ('P101','NUT','RED',12,'LONDON');  
  INSERT INTO MANN_PART VALUES ('P102','BOLT','GREEN',17,'PARIS');
  INSERT INTO MANN_PART VALUES ('P103','SCREW','BLUE',17,'ROME'); 
  INSERT INTO MANN_PART VALUES ('P104','SCREW','RED',14,'LONDON');
  INSERT INTO MANN_PART VALUES ('P105','CAM','BLUE',12,'PARIS');  
  INSERT INTO MANN_PART VALUES ('P106','COG','RED',19,'LONDON');  
  INSERT INTO MANN_PART VALUES ('P201','NUT','RED',12,'LONDON');  
  INSERT INTO MANN_PART VALUES ('P202','BOLT','GREEN',17,'PARIS');
  INSERT INTO MANN_PART VALUES ('P203','SCREW','BLUE',17,'ROME'); 
  INSERT INTO MANN_PART VALUES ('P204','SCREW','RED',14,'LONDON');
  INSERT INTO MANN_PART VALUES ('P205','CAM','BLUE',12,'PARIS');  
  INSERT INTO MANN_PART VALUES ('P206','COG','RED',19,'LONDON');  

OZA058.PENCHAL.SETUP(PART)
-------------

 CREATE TABLE MANN_SUPP                          
   (SUPP_NO        CHAR(5)  NOT NULL,              
    SUPP_NAME      CHAR(20) NOT NULL,              
    SUPP_STATUS    SMALLINT NOT NULL,              
    SUPP_CITY      CHAR(15) NOT NULL,              
    PRIMARY KEY (SUPP_NO) )                        
    IN MANNDB.SUPPPRTS;                            
 CREATE UNIQUE INDEX SUPPIX ON MANN_SUPP(SUPP_NO)
         USING STOGROUP MANNSTG                 
         PRIQTY 12                              
         SECQTY 100                             
         CLUSTER                                
         (PART 1 VALUES ('S100'),               
          PART 2  VALUES ('S200'),              
          PART 3  VALUES ('S300'))              
         BUFFERPOOL BP0                                  
         COPY YES                                        
         CLOSE NO;                                       
  INSERT INTO MANN_SUPP VALUES ('S1','SMITH',20,'LONDON'); 
  INSERT INTO MANN_SUPP VALUES ('S2','JONES',10,'PARIS');  
  INSERT INTO MANN_SUPP VALUES ('S3','BLAKE',30,'PARIS');  
  INSERT INTO MANN_SUPP VALUES ('S4','CLARK',20,'LONDON'); 
  INSERT INTO MANN_SUPP VALUES ('S5','ADAMS',30,'ATHENS'); 
                                                           
  INSERT INTO MANN_SUPP VALUES ('S101','SMITH',20,'LONDON');
  INSERT INTO MANN_SUPP VALUES ('S102','JONES',10,'PARIS');
  INSERT INTO MANN_SUPP VALUES ('S103','BLAKE',30,'PARIS');
  INSERT INTO MANN_SUPP VALUES ('S104','CLARK',20,'LONDON');
  INSERT INTO MANN_SUPP VALUES ('S105','ADAMS',30,'ATHENS');
                                                           
  INSERT INTO MANN_SUPP VALUES ('S201','SMITH',20,'LONDON');
  INSERT INTO MANN_SUPP VALUES ('S202','JONES',10,'PARIS');
  INSERT INTO MANN_SUPP VALUES ('S203','BLAKE',30,'PARIS');
  INSERT INTO MANN_SUPP VALUES ('S204','CLARK',20,'LONDON');
  INSERT INTO MANN_SUPP VALUES ('S205','ADAMS',30,'ATHENS');
-----------------
 CREATE TABLE MANN_SHIP                                   
  (SUPP_NO        CHAR(5)  NOT NULL,                       
   PART_NO        CHAR(6)  NOT NULL,                       
   QTY            INTEGER  NOT NULL,                       
   PRIMARY KEY (SUPP_NO,PART_NO),                          
   FOREIGN KEY (SUPP_NO) REFERENCES MANN_SUPP,             
   FOREIGN KEY (PART_NO) REFERENCES MANN_PART)             
   IN MANNDB.SHIPPRTS;                                     
                                                         
 CREATE UNIQUE INDEX SHIPIX ON MANN_SHIP(SUPP_NO,PART_NO)
         USING STOGROUP MANNSTG                          
         PRIQTY 12                                       
         SECQTY 100                                      
         CLUSTER                                         
         (PART 1 VALUES ('S100'),                        
          PART 2  VALUES ('S200'),           
          PART 3  VALUES ('S300'))           
         BUFFERPOOL BP0                      
         COPY YES                            
         CLOSE NO;                           
  INSERT INTO MANN_SHIP VALUES ('S1','P1',300);
  INSERT INTO MANN_SHIP VALUES ('S1','P2',300);
  INSERT INTO MANN_SHIP VALUES ('S1','P3',300);
  INSERT INTO MANN_SHIP VALUES ('S1','P4',300);
  INSERT INTO MANN_SHIP VALUES ('S1','P5',300);
  INSERT INTO MANN_SHIP VALUES ('S1','P6',300);
  INSERT INTO MANN_SHIP VALUES ('S2','P1',300);
  INSERT INTO MANN_SHIP VALUES ('S2','P2',300);
  INSERT INTO MANN_SHIP VALUES ('S3','P2',300);
  INSERT INTO MANN_SHIP VALUES ('S4','P2',300);
  INSERT INTO MANN_SHIP VALUES ('S4','P4',300);
                                                    
  INSERT INTO MANN_SHIP VALUES ('S101','P101',300); 
  INSERT INTO MANN_SHIP VALUES ('S101','P102',300); 
  INSERT INTO MANN_SHIP VALUES ('S101','P103',300); 
  INSERT INTO MANN_SHIP VALUES ('S101','P104',300); 
  INSERT INTO MANN_SHIP VALUES ('S101','P105',300); 
  INSERT INTO MANN_SHIP VALUES ('S101','P106',300); 
  INSERT INTO MANN_SHIP VALUES ('S102','P101',300); 
  INSERT INTO MANN_SHIP VALUES ('S102','P102',300); 
  INSERT INTO MANN_SHIP VALUES ('S103','P102',300); 
  INSERT INTO MANN_SHIP VALUES ('S104','P102',300); 
  INSERT INTO MANN_SHIP VALUES ('S104','P104',300); 
                                                    
  INSERT INTO MANN_SHIP VALUES ('S201','P201',300); 
  INSERT INTO MANN_SHIP VALUES ('S201','P202',300); 
  INSERT INTO MANN_SHIP VALUES ('S201','P203',300); 
  INSERT INTO MANN_SHIP VALUES ('S201','P204',300);
  INSERT INTO MANN_SHIP VALUES ('S201','P205',300);
  INSERT INTO MANN_SHIP VALUES ('S201','P206',300);
  INSERT INTO MANN_SHIP VALUES ('S202','P201',300);
  INSERT INTO MANN_SHIP VALUES ('S202','P202',300);
  INSERT INTO MANN_SHIP VALUES ('S203','P202',300);
  INSERT INTO MANN_SHIP VALUES ('S204','P202',300);
  INSERT INTO MANN_SHIP VALUES ('S204','P204',300);
  INSERT INTO MANN_SHIP VALUES ('S205','P205',300);
-----------------------------------

Table column types:
COLTYPE    CHAR(8)   NOT NULL
The type of the column specified in the definition of the column:
INTEGER - Large integer
SMALLINT - Small integer
FLOAT - Floating-point
CHAR - Fixed-length character string
VARCHAR - Varying-length character string
LONGVAR - Varying-length character string (for columns that
were added before Version 9)
DECIMAL - Decimal
GRAPHIC - Fixed-length graphic string
VARG - Varying-length graphic string
LONGVARG - Varying-length graphic string (for columns that
were added before Version 9)
DATE - Date
TIME - Time
TIMESTMP - Timestamp
BLOB - Binary large object
CLOB - Character large object
DBCLOB - Double-byte character large object
ROWID - Row ID data type
DISTINCT - Distinct type
XML - XML data type
BIGINT - Big integer
BINARY - Fixed-length binary string
VARBIN -Varying-length binary string
DECFLOAT - Decimal floating point
Column Lengths:
---------------------
Length attribute of the column or, in the case of a decimal
column, its precision. The number does not include the
internal prefixes that are used to record the actual length
and null state, where applicable.
INTEGER -
SMALLINT -
FLOAT 4 or 8
CHAR - Length of string
VARCHAR - length of string
LONGVAR - length of string (for columns that
were added before Version 9)
DECIMAL - of number
GRAPHIC - of DBCS characters
VARGRAPHIC - number of DBCS characters
LONGVARG - number of DBCS characters (for
columns that were added before Version 9)
DATE - 4
TIME  - 3
TIMESTAMP -
BLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the LOB
column is found in LENGTH2.
CLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the CLOB
column is found in LENGTH2.
DBCLOB - - For a table, a field of length of 4 is stored in
the base table. The maximum length of the
DBCLOB column is found in LENGTH2.
ROWID - - The maximum length of the stored portion
of the identifier.
DISTINCT -length of the source data type.
XML - 6
BIGINT -
BINARY - of string
VARBINARY -length of string
DECFLOAT - 8 or 16
Control Cards
-------------
COPY
COPY TABLESPACE MANNDB.MANNSITS
     SHRLEVEL REFERENCE       
     FULL YES                 

COPYINC
-------
COPY TABLESPACE MANNDB.MANNSITS
     SHRLEVEL REFERENCE        
     FULL NO                   

COPYRPT
-------
COPY TABLESPACE MANNDB.MANNSITS
     SHRLEVEL REFERENCE        
     CHANGELIMIT(10,15)        
     REPORT ONLY               

LOADCD
------
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES                
 EBCDIC  CCSID(00037,00000,00000)                          
 INTO TABLE "OZA059  "."SIMP_SUPP         "                
 WHEN(00001:00002 = X'0007')                               
 ( "SUPP_NO           " POSITION(  00003:00007) CHAR(00005)
 , "SUPP_NAME         " POSITION(  00008:00027) CHAR(00020)
 , "SUPP_STATUS       " POSITION(  00028:00029) SMALLINT   
 , "SUPP_CITY         " POSITION(  00030:00044) CHAR(00015)
 )                                                         
 INTO TABLE "OZA059  "."SIMP_PARTS        "                
 WHEN(00001:00002 = X'0010')                               
 ( "PART_NO           " POSITION(  00003:00008) CHAR(00006)
 , "PART_NAME         " POSITION(  00009:00028) CHAR(00020)
 , "PART_COLOR        " POSITION(  00029:00034) CHAR(00006)
 , "PART_WEIGHT       " POSITION(  00035:00036) SMALLINT   
 , "PART_CITY         " POSITION(  00037:00051) CHAR(00015)
 )                                                         
 INTO TABLE "OZA059  "."SIMP_SHIP         "                
 WHEN(00001:00002 = X'0005')                               
 ( "SUPP_NO           " POSITION(  00003:00007) CHAR(00005)
 , "PART_NO           " POSITION(  00008:00013) CHAR(00006)
 , "QTY               " POSITION(  00014:00017) INTEGER    
 )                                                         
QUEISCE
-------
QUIESCE TABLESPACE MANNDB.SIMPTS
WRITE YES                       
RBINDEX
-------
REBUILD INDEX (OZA059.SISUPP,OZA059.SIPARTS,OZA059.SISHIP)
RECOVER
--------
RECOVER TABLESPACE MANNDB.MANNSITS           
        SHRLEVEL REFERENCE                   
        TOCOPY 'OZADB2.MANNDB.SITS.FIC.D0208A'
RUNSTAT
-------
RUNSTATS TABLESPACE MANNDB.MANNSITS

UNLOAD
------
UNLOAD TABLESPACE MANNDB.MANNSITS

CHCKDATA
--------
CHECK DATA TABLESPACE MANNDB.SHIPPRTS
CHCKINDX
--------
CHECK INDEX INDEX OA059.SHIPIX
COPYALL
-------
COPY TABLESPACE MANNDB.SUPPPRTS
     COPYDDN(SYSCOPY1)         
     TABLESPACE MANNDB.PARTPRTS
     COPYDDN(SYSCOPY2)         
     TABLESPACE MANNDB.SHIPPRTS
     COPYDDN(SYSCOPY3)         
     INDEX OZA059.SUPPIX       
     COPYDDN(SYSCOPY4)         
     INDEX OZA059.PARTIX       
     COPYDDN(SYSCOPY5)         
     INDEX OZA059.SHIPIX       
     COPYDDN(SYSCOPY6)         
PARALLEL(4) SHRLEVEL REFERENCE 
FULL YES                       

COPYPART
--------
LISTDEF COPYLIST                      
    INCLUDE TABLESPACE MANNDB.SUPPPRTS
    INCLUDE TABLESPACE MANNDB.PARTPRTS
    INCLUDE TABLESPACE MANNDB.SHIPPRTS
    INCLUDE INDEX MANNDB.SUPPIX       
    INCLUDE INDEX MANNDB.PARTIX       
    INCLUDE INDEX MANNDB.SHIPIX       
COPY LIST COPYLIST COPYDDN(T1,T1)     
PARALLEL(4) SHRLEVEL REFERENCE        
FULL YES                              

COPYPART1
---------
COPY TABLESPACE MANNDB.SUPPPRTS DSNUM 1
SHRLEVEL REFERENCE                    
FULL YES                              

DIAGNOS
-------
DIAGNOSE                   
    COPY INDEX OZA059.SHIPIX
DIAGNOSE END               
 
LOAD
----
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES                
 EBCDIC  CCSID(00037,00000,00000)                          
 INTO TABLE "OZA059  "."SUPPLIER          "                
 WHEN(00001:00002 = X'0004')                               
 ( "SNO               " POSITION(  00003:00007) CHAR(00005)
 , "SNAME             " POSITION(  00008:00027) CHAR(00020)
 , "STATUS            " POSITION(  00028:00029) SMALLINT   
 , "CITY              " POSITION(  00030:00044) CHAR(00015)
 )                                                         
 INTO TABLE "OZA059  "."PARTS             "                
 WHEN(00001:00002 = X'0007')                               
 ( "PNO               " POSITION(  00003:00008) CHAR(00006)
 , "PNAME             " POSITION(  00009:00028) CHAR(00020)
 , "COLOR             " POSITION(  00029:00034) CHAR(00006)
 , "WEIGHT            " POSITION(  00035:00036) SMALLINT   
 , "CITY              " POSITION(  00037:00051) CHAR(00015)
 )                                                         
 INTO TABLE "OZA059  "."SHIPMENT          "                
 WHEN(00001:00002 = X'000A')                               
 ( "SNO               " POSITION(  00003:00007) CHAR(00005)
 , "PNO               " POSITION(  00008:00013) CHAR(00006)
 , "QUANTITY          " POSITION(  00014:00017) INTEGER    
 )                                                         

LOADP
-----
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES                
 EBCDIC  CCSID(00037,00000,00000)                          
 INTO TABLE "OZA059  "."PARTS             "                
 WHEN(00001:00002 = X'0007')                               
 ( "PNO               " POSITION(  00003:00008) CHAR(00006)
 , "PNAME             " POSITION(  00009:00028) CHAR(00020)
 , "COLOR             " POSITION(  00029:00034) CHAR(00006)
 , "WEIGHT            " POSITION(  00035:00036) SMALLINT   
 , "CITY              " POSITION(  00037:00051) CHAR(00015)
 )                                                         

LOADSH
------
LOAD DATA INDDN SYSREC   LOG NO  RESUME YES                
 EBCDIC  CCSID(00037,00000,00000)                          
 INTO TABLE "OZA059  "."SHIPMENT          "                
 WHEN(00001:00002 = X'000A')                               
 ( "SNO               " POSITION(  00003:00007) CHAR(00005)
 , "PNO               " POSITION(  00008:00013) CHAR(00006)
 , "QUANTITY          " POSITION(  00014:00017) INTEGER    
 )                                                         

MERGCPY
-------
MERGECOPY TABLESPACE MANNDB.SUPPPRTS
NEWCOPY YES                        
MODIFY
------
MODIFY RECOVERY TABLESPACE VSRDB.TS1
DELETE AGE(*)                      

QCEMSUP
-------
QUIESCE TABLESPACE MANNDB.SUPPPRTS
        TABLESPACE MANNDB.PARTPRTS
        TABLESPACE MANNDB.SHIPPRTS

REBLDIX
-------
REBUILD INDEX (OZA059.SHIPIX)

RECOVER
-------
RECOVER TABLESPACE VSRDB.TS1
TORBA X'014A8579FDBC'       

REPORT
------
REPORT TABLESPACESET TABLESPACE MANNDB.SUPPPRTS
RSTATRPT
--------
RUNSTATS TABLESPACE MANNDB.SUPPPRTS
REPORT YES                        
UPDATE NONE                       
 
RSTATUPD
--------
LISTDEF STATLIST                          
        INCLUDE TABLESPACE MANNDB.SUPPPRTS
        INCLUDE TABLESPACE MANNDB.PARTPRTS
        INCLUDE TABLESPACE MANNDB.SHIPPRTS
RUNSTATS LIST STATLIST                    
REPORT YES                                
UPDATE YES                                

SUPPPART
--------
COPY TABLESPACE MANNDB.SUPPPRTS DSNUM 1
     COPYDDN(SYSCOPY1)                 
     TABLESPACE MANNDB.SUPPPRTS DSNUM 2
     COPYDDN(SYSCOPY2)                 
     TABLESPACE MANNDB.SUPPPRTS DSNUM 3
     COPYDDN(SYSCOPY3)                 
SHRLEVEL REFERENCE                     
FULL YES                               

SUPPPRT1
--------
RECOVER TABLESPACE MANNDB.SUPPPRTS DSNUM 1

UNLOAD
-------
UNLOAD TABLESPACE VSRDB1.TS1
---------------------------------------------
-START DATABASE(PAWANDB)                       
-START DATABASE(VSRDB1) SPACENAM(TS1)          
-DISPLAY DATABASE(MANNDB) SPACENAM(*) RESTRICT 
                                               
-START DATABASE(DB1) SPACENAM(GVRKTS)          
                                               
-DIS DB(DBCHA) SPACENAM(TS10)                  
                                               
-DIS BUFFERPOOL(BP0) DETAIL                    
                                               
-DIS BUFFERPOOL(BP8K0)                     
Ver 7.1 Utilities
------------------
CHECK DATA,
CHECK INDEX,
CHECK LOB,   
COPY,
DIAGNOSE,
LOAD,
MERGE,
MODIFY,  
QUIESCE,
REBUILD,
RECOVER,
REORG INDEX,
REORG LOB,
REORG TABLESPACE,
REPORT,  
REPAIR,
RUNSTATS,
STOSPACE,
UNLOAD)   

Version 9.0
------------
DB2 ONLINE UTILITIES
---------------------
1. BACKUP SYSTEM
2. CATENFM
3. CATMAINT
4. CHECK DATA
5. CHECK INDEX
6. CHECK LOB
7. COPY
8. COPYTOCOPY
9. DIAGNOSE
10.EXEC SQL
11. LISTDEF
12. LOAD
13. MERGECOPY
14. MODIFY RECOVERY
15. MODIFY STASTICS
16. OPTIONS
17. QUIESCE
18. REBUILD INDEX
19. RECOVER
20. RECOVER INDEX
21. REORG TABLESPACE
22. REPAIR
23. REPORT
24. RESTORE SYSTEM
25. RUNSTATS
26. STOSPACE
27. TEMPLATE
28. UNLOAD

DB2 STAND-ALONE UTILITIES
-------------------------
1. DSNJCNOB
2. DSNJLOGF
3. DSNJU003
4. DSNJU004
5. DSN1CHKR
6. DSN1COMP
7. DSN1COPY
8. DSN1LOGP
9. DSN1PRNT
10. DSN1SDMP

EXPORT REPORT TO 'OZA059.MANN.RPT1'

QMF Commands
------------
 1. ADD command     
 2. BACKWARD command
 3. BOTTOM command  
 4. BATCH command   
 5. CANCEL command  
 6. CHANGE command  
 7. CHECK command   
 8. CLEAR command   
 9. CONNECT command 
10. CONVERT command 
11. DELETE command  
12. DESCRIBE command   
13. DISPLAY command    
14. DPRE command       
15. DRAW command       
16. EDIT object command
17. EDIT TABLE command 
18. END command        
19. ENLARGE command    
20. EXPORT command     
21. ERASE command      
22. EXTRACT command    
23. FORWARD command   
24. GET GLOBAL command
25. GETQMF macro      
26. HELP command      
27. IMPORT command    
28. INSERT command    
29. INTERACT command  
30. ISPF command      
31. LAYOUT command    
32. LEFT command      
33. LIST command      
34. MESSAGE command     
35. NEXT command        
36. PREVIOUS command    
37. PRINT command       
38. QMF command         
39. REDUCE command      
40. REFRESH command     
41. RESET GLOBAL command
42. RESET command       
43. RETRIEVE command    
44. RIGHT command       
45. RUN command        
46. SAVE command       
47. SEARCH command     
48. SET GLOBAL command 
49. SET PROFILE command
50. SHOW command       
51. SORT command       
52. SPECIFY command    
53. START command      
54. STATE command      
55. SWITCH command     
56. TOP command
57. TSO command

 
 

Table column types:
COLTYPE    CHAR(8)   NOT NULL
The type of the column specified in the definition of the column:
INTEGER - Large integer
SMALLINT - Small integer
FLOAT - Floating-point
CHAR - Fixed-length character string
VARCHAR - Varying-length character string
LONGVAR - Varying-length character string (for columns that
were added before Version 9)
DECIMAL - Decimal
GRAPHIC - Fixed-length graphic string
VARG - Varying-length graphic string
LONGVARG - Varying-length graphic string (for columns that
were added before Version 9)
DATE - Date
TIME - Time
TIMESTMP - Timestamp
BLOB - Binary large object
CLOB - Character large object
DBCLOB - Double-byte character large object
ROWID - Row ID data type
DISTINCT - Distinct type
XML - XML data type
BIGINT - Big integer
BINARY - Fixed-length binary string
VARBIN -Varying-length binary string
DECFLOAT - Decimal floating point
Column Lengths:
---------------------
Length attribute of the column or, in the case of a decimal
column, its precision. The number does not include the
internal prefixes that are used to record the actual length
and null state, where applicable.
INTEGER -
SMALLINT -
FLOAT 4 or 8
CHAR - Length of string
VARCHAR - length of string
LONGVAR - length of string (for columns that
were added before Version 9)
DECIMAL - of number
GRAPHIC - of DBCS characters
VARGRAPHIC - number of DBCS characters
LONGVARG - number of DBCS characters (for
columns that were added before Version 9)
DATE - 4
TIME  - 3
TIMESTAMP -
BLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the LOB
column is found in LENGTH2.
CLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the CLOB
column is found in LENGTH2.
DBCLOB - - For a table, a field of length of 4 is stored in
the base table. The maximum length of the
DBCLOB column is found in LENGTH2.
ROWID - - The maximum length of the stored portion
of the identifier.
DISTINCT -length of the source data type.
XML - 6
BIGINT -
BINARY - of string
VARBINARY -length of string
DECFLOAT - 8 or 16
=============================

CATALOG TABLES : 108  in db2 ver 9.0
------------------------------------
SYSIBM.IPLIST
SYSIBM.IPNAMES
SYSIBM.LOCATIONS
SYSIBM.LULIST
SYSIBM.LUMODES
SYSIBM.LUNAMES
SYSIBM.MODESELECT
SYSIBM.SYSAUXRELS
SYSIBM.SYSCHECKDEP
SYSIBM.SYSCHECKS
SYSIBM.SYSCHECKS2
SYSIBM.SYSCOLAUTH
SYSIBM.SYSCOLDIST
SYSIBM.SYSCOLDISTSTATS
SYSIBM.SYSCOLDIST_HIST
SYSIBM.SYSCOLSTATS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSCOLUMNS_HIST
SYSIBM.SYSCONSTDEP
SYSIBM.SYSCONTEXT
SYSIBM.SYSCONTEXTAUTHIDS
SYSIBM.SYSCOPY
SYSIBM.SYSCTXTTRUSTATTRS
SYSIBM.SYSDATABASE
SYSIBM.SYSDATATYPES
SYSIBM.SYSDBAUTH
SYSIBM.SYSDBRM
SYSIBM.SYSDEPENDENCIES
SYSIBM.SYSDUMMY1
SYSIBM.SYSENVIRONMENT
SYSIBM.SYSFIELDS
SYSIBM.SYSFOREIGNKEYS
SYSIBM.SYSINDEXES
SYSIBM.SYSINDEXES_HIST
SYSIBM.SYSINDEXPART
SYSIBM.SYSINDEXPART_HIST
SYSIBM.SYSINDEXSPACESTATS
SYSIBM.SYSINDEXSTATS
SYSIBM.SYSINDEXSTATS_HIST
SYSIBM.SYSJARCLASS_SOURCE
SYSIBM.SYSJARCONTENTS
SYSIBM.SYSJARDATA
SYSIBM.SYSJAROBJECTS
SYSIBM.SYSJAVAOPTS
SYSIBM.SYSJAVAPATHS
SYSIBM.SYSKEYCOLUSE
SYSIBM.SYSKEYS
SYSIBM.SYSKEYTARGETS
SYSIBM.SYSKEYTARGETSTATS
SYSIBM.SYSKEYTARGETS_HIST
SYSIBM.SYSKEYTGTDIST
SYSIBM.SYSKEYTGTDISTSTATS
SYSIBM.SYSKEYTGTDIST_HIST
SYSIBM.SYSLOBSTATS
SYSIBM.SYSLOBSTATS_HIST
SYSIBM.SYSOBJROLEDEP
SYSIBM.SYSPACKAGE
SYSIBM.SYSPACKAUTH
SYSIBM.SYSPACKDEP
SYSIBM.SYSPACKLIST
SYSIBM.SYSPACKSTMT
SYSIBM.SYSPARMS
SYSIBM.SYSPKSYSTEM
SYSIBM.SYSPLAN
SYSIBM.SYSPLANAUTH
SYSIBM.SYSPLANDEP
SYSIBM.SYSPLSYSTEM
SYSIBM.SYSRELS
SYSIBM.SYSRESAUTH
SYSIBM.SYSROLES
SYSIBM.SYSROUTINEAUTH
SYSIBM.SYSROUTINES
SYSIBM.SYSROUTINESTEXT
SYSIBM.SYSROUTINES_OPTS
SYSIBM.SYSROUTINES_SRC
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSSEQUENCEAUTH
SYSIBM.SYSSEQUENCES
SYSIBM.SYSSEQUENCESDEP
SYSIBM.SYSSTMT
SYSIBM.SYSSTOGROUP
SYSIBM.SYSSTRINGS
SYSIBM.SYSSYNONYMS
SYSIBM.SYSTABAUTH
SYSIBM.SYSTABCONST
SYSIBM.SYSTABLEPART
SYSIBM.SYSTABLEPART_HIST
SYSIBM.SYSTABLES
SYSIBM.SYSTABLESPACE
SYSIBM.SYSTABLESPACESTATS
SYSIBM.SYSTABLES_HIST
SYSIBM.SYSTABSTATS
SYSIBM.SYSTABSTATS_HIST
SYSIBM.SYSTRIGGERS
SYSIBM.SYSUSERAUTH
SYSIBM.SYSVIEWDEP
SYSIBM.SYSVIEWS
SYSIBM.SYSVOLUMES
SYSIBM.SYSXMLRELS
SYSIBM.SYSXMLSTRINGS
SYSIBM.USERNAMES
SYSIBM.XSRCOMPONENT
SYSIBM.XSROBJECTS
SYSIBM.XSROBJECTCOMPONENTS
SYSIBM.XSROBJECTGRAMMAR
SYSIBM.XSROBJECTHIERARCHIES
SYSIBM.XSROBJECTPROPERTY
SYSIBM.XSRPROPERTY

Table column types:
-------------------
COLTYPE    CHAR(8)   NOT NULL
The type of the column specified in the definition of the column:
INTEGER - Large integer
SMALLINT - Small integer
FLOAT - Floating-point
CHAR - Fixed-length character string
VARCHAR - Varying-length character string
LONGVAR - Varying-length character string (for columns that
were added before Version 9)
DECIMAL - Decimal
GRAPHIC - Fixed-length graphic string
VARG - Varying-length graphic string
LONGVARG - Varying-length graphic string (for columns that
were added before Version 9)
DATE - Date
TIME - Time
TIMESTMP - Timestamp
BLOB - Binary large object
CLOB - Character large object
DBCLOB - Double-byte character large object
ROWID - Row ID data type
DISTINCT - Distinct type
XML - XML data type
BIGINT - Big integer
BINARY - Fixed-length binary string
VARBIN -Varying-length binary string
DECFLOAT - Decimal floating point
Column Lengths:
---------------------
Length attribute of the column or, in the case of a decimal
column, its precision. The number does not include the
internal prefixes that are used to record the actual length
and null state, where applicable.
INTEGER -
SMALLINT -
FLOAT 4 or 8
CHAR - Length of string
VARCHAR - length of string
LONGVAR - length of string (for columns that
were added before Version 9)
DECIMAL - of number
GRAPHIC - of DBCS characters
VARGRAPHIC - number of DBCS characters
LONGVARG - number of DBCS characters (for
columns that were added before Version 9)
DATE - 4
TIME  - 3
TIMESTAMP -
BLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the LOB
column is found in LENGTH2.
CLOB - 4 - For a table, a field of length of 4 is stored in
the base table. The maximum length of the CLOB
column is found in LENGTH2.
DBCLOB - - For a table, a field of length of 4 is stored in
the base table. The maximum length of the
DBCLOB column is found in LENGTH2.
ROWID - - The maximum length of the stored portion
of the identifier.
DISTINCT -length of the source data type.
XML - 6
BIGINT -
BINARY - of string
VARBINARY -length of string
DECFLOAT - 8 or 16
-----------------------
 
 
 

 
 
 
 
 

 



Comments