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:
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, 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:
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
SQL statements for data manipulation
SQL statements for data control
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). 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
----------------------- |