10.4 ̬SQLCӣʵʾ˾̬SQL÷LASTNAMEеWu¼FIRSTNAMEеֵӡϢ

/**********************************************************************
*****
*****
*****ԴļΪstatic.sqc 1.4
*****
*************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "util.h"

#ifdef DB268K
#include <LibraryManager.h>
#endif

EXEC SQL INCLUDE SQLCA;
#define CHECKERR(CE_STR)
if (check_error(CE_STR, &sqlca)!=0)
return 1;

int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char firstname[13];
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;

#ifdef DB268K
/*Ϊ68KκAPI֮ǰҪʼLibrary Manger*/
InitLibraryManager(0, kCurrentZone, kNormalMemory);
Atexit(CleanupLibraryManager);
#endif
printf("Sample C program: STATIC\n");
if(argc==1)
{
EXEC SQL CONNECT TO sample;
CHECKERR("CONNECT TO SAMPLE");
}
else if(argc==3)
{
strcpy(userid, argv[1]);
strcpy(passwd,argv[2]);
EXEC SQL CONNECT TO sample USER: userid USING: passwd;
CHECKERR("CONNECT TO SAMPLE");
}
else
{
printf("\nUSAGE: static [userid passwd]\n\n");
return 1;
}
EXEC SQL SELECT FIRSTNAME INTO: firstname
FROM employee
WHERE LASTNAME='Wu';
CHECKERR("SELECT statement");

Printf("First name=%s\n", firstname);

EXEC SQL CONNECT RESET;
CHECKERR("CONNECT RESET");
return 0;
}

10.5 佫⣨WuĹԱĹʸempsal

SELECT SALARY
INTO: empsal
FROM EMPLOYEE
WHERE LASTNAME='Wu'

10.6 ִ䣬ѯԱнˮĹԱ

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC

10.7 TEMPL1000¼ҪйԱĹʣӦÿ100оҪִһCOMMIT䡣
ͨWITH HOLDѡα

EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR
SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY
FROM TEMPL FOR UPDATE OF SALARY

ִ䣬α꣬ÿδӽȡһݡ

EXEC SQL OPEN EMPLUPDT
EXEC SQL FETCH EMPLUPDT
INTO: upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage

ִ䣬ʹôWHERE CURRENT OFѡUPDATEDELETE䣬µǰС

EXEC SQL UPDATE TEMPL SET SALARY = : newsalary
WHERE CURRENT OF EMPLUPDT

10.8 αCʵCURSOR.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include util.h

#ifdef DB268K
#include <LibraryManager.h>
#endif

EXEC SQL INCLUDE SQLCA;

#define CHECKERR(CE_STR)
if(check_error(CE_STR, &sqlca)!=0)
return 1;

int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char pname[10];
short dept;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;

#ifdef DB268K
/*68KнκAPIʱҪװ*/
InitLibraryManager(0, kCurrentZone, kNormalMemory);
Atexit(CleanupLibraryManager);
#endif

printf(ʵCCURSOR\n);

if(argc= =1)
{
EXEC SQL CONNECT TO sample;
CHECKERR(ӵSample);
}
}

else if(argc= =3)
{
strcpy(userid, argv[1]);
strcpy(passwd, argv[2]);
EXEC SQL CONNECT TO sample USER: userid USING: passwd;
CHECKERR(ӵSample);
}
else
{
printf(\nUSAGE: cursor [userid passwd]\n\n);
return 1;

EXEC SQL DECLARE c1 CURSOR FOR                                    
SELECT name, dept FROM staff WHERE job=Mgr
FOR UPDATE OF job;

EXEC SQL OPEN c1;                                                   
CHECKERR(OPEN CURSOR);

do
{
EXEC SQL FETCH c1 INTO: pname, :dept;                                   
if(SQLCODE!=0)
break;

printf(%-10.10s in dept. %2d will be demoted to clerk\n, pname, dept);
}while(1);

EXEC SQL CLOSE c1;                                                   
CHECKERR(CLOSE CURSOR);

EXEC SQL ROLLBACK;
CHECKERR(ROLLBACK);
printf(\nOn second thought  changes rolled back.\n);

EXEC SQL CONNECT RESET;
CHECKERR(CONNECT RESET);
Return 0;
}

10.9 αJavaʵCursor.sqlj
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator CursorByName(String name, short dept);
#sql iterator CursorByPos(String, short);
class Cursor
{
static
{
try
{
Class.forName(COM.ibm.db2.jdbc.app.DB2Driver).newInstance();
}
catch(Exception e)
{
System.out.println(\nError loading DB2 Driver\n);
System.out.println(e);
System.exit(1);
}
}
}

public static void main(String argv[])
{
try
{
System.out.println(Java Cursor Sample);

String url=jdbc: db2: sample;
//jdbc: db2: dbname
Connection con=null;

//
if(argv.length = = 0)
{
//ĬϵID
con=DriverManager.getConnection(url);
}
else if(argv.length = = 2)
{
con= DriverManager.getConnection(url, userid, passwd);
}
else
{
throw new Exception(Usage: java Cursor [username password]);
}

//Ĭϵ
DefaultContent ctx=new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
Con.setAutoCommit(false);

//α
try
{
CursorByName cursorByName;
CursorByPos cursorByPos;

String name=null;
Short dept=0;

//ʹαJDBC
System.out.println(\nUsing the JDBC ResultSet cursor method);
System.out.println( with a bind by name cursor  \n);

#sql cursorByName=
{
SELECT name, dept FROM staff
WHERE job=Mgr FOR UPDATE OF job
};																
while(cursorByName.next())													
{
name=cursorByName.name();												
dept=cursorByName.dept();

System.out.print( name=+name);
System.out.print( dept=+dept);
System.out.print(\n);
}
cursorByName.close();														

//SQLJʹα귽
System.out.println(\nUsing the SQLJ iterator cursor method);
System.out.println( with a bind by position cursor \n);

#sql cursorByPos=
{
SELECT name, dept FROM staff
WHERE job=Mgr FOR UPDATE OF job
};
																
while(true)
{
#sql
{
FETCH: cursorByPos INTO: name, :dept
};																

if(cursorByPos.endFetch())
break;
System.out.print( name= +name);
System.out.print( dept= +dept);
System.out.print(\n);
}
cursorByPos.close();														
}
catch(Exception e)
{
throw e;
}
finally
{
//ع
System.out.println(\nع񡭡);
#sql
{
ROLLBACK;
}
System.out.println(ɻع);
}
}
catch(Exception e)
{
System.out.println(e);
}
}
}

10.10 C԰OPENFTCHopenftch.sqcӣͨαӱѡУα꣬ӱȡСȡÿһм飬жǷҪɾ߸£жȽϼ򵥡

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include util.h

#ifdef DB268K
/*Ҫ68KӦóASLM*/
#include <LibraryManager.h>
#endif

EXEC SQL INCLUDE SQLCA;

#define CHECKERR(CE_STR)
if(check_error(CE_STR, &sqlca)!=0)
return 1;

int main(int argc, char *argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
char pname[10];
short dept;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;

#ifdef DB268K
InitLibraryManager(0, kCurrentZone, kNormalMemory);
Atexit(CleanupLibraryManager);
#endif

printf(COPENFTCH\n);

if (argc = = 1)
{
EXEC SQL CONNECT TO sample;
CHECKERR(ӵSAMPLEݿ⡱);
}
else if (argc = = 3)
{
strcpy(userid, argv[1]);
strcpy(passwd, argv[2]);
EXEC SQL CONNECT TO sample USER: userid USING: passwd;
CHECKERR(CONNECT TO SAMPLE);
}
else
{
printf(\nUSAGE:openftch [userid passwd]\n\n);
return 1;
}
EXEC SQL DECLARE c1 CURSOR FOR										(1)
SELECT name, dept FROM staff WHERE job=Mgr
FOR UPDATE OF job;
EXEC SQL OPEN c1;														(2)
CHECKERR(OPEN CURSOR);

Do
{
EXEC SQL FETCH c1 INTO: pname, :dept;										(3)
if (SQLCODE != 0)
break;

if (dept > 40)
printf(%-10.10s in dept. %2d will be demoted to clerk\n, pname, dept);
EXEC SQL UPDATE staff SET job = Clerk									(4)
WHERE CURRENT OF c1;
CHECKERR(UPDATE STAFF);
}
else 
{
printf(%-10.10s in dept. %2d will be DELETED!\n, pname, dept);
EXEC SQL DELETE FROM staff WHERE CURRENT OF c1;
CHECKERR(DELETE);
}
}while(1);

EXEC SQL CLOSE c1;														(5)
CHECKERR(CLOSE CURSOR);

EXEC SQL ROLLBACK;
CHECKERR(ROLLBACK);
printf(\nOn second thought  changes rolled back.\n);

EXEC SQL CONNECT RESET;
CHECKERR(CONNECT RESET);
Return 0;
}

10.11 10.10JavaԴ汾Openftch.sqljOpF_Curs.sqlj
OpF_Curs.sqljԴ¡
Import sqlj.runtime.ForUpdate;
#sql public iterator OpF_Curs implements ForUpdate (String, short);

Openftch.sqljԴ¡
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

class Openftch
{
static
{
try
{
Class.forName(COM.ibm.db2.jdbc.app.DB2Driver).newInstance();
}
catch(Exception e)
{
System.out.println(\n Error loading DB2 Driver  \n);
System.out.println(e);
System.exit(1);
}
}

public static void main(String argv[])
{
try
{
System.out.println(Java Openftchʵ);
String url=jdbc: db2: sample;
//URLΪjdbc: db2: dbname
Connection con=null;

//
if (argv.length = = 0)
{
//ʹĬϵidӡ
con= DriverManager.getConnection (url);
}
else if (argv.length = = 2)
{
String userid=argv[0];
String passwd=argv[1];
//ʹûṩûǸ
con=DriverManager.getConnection(url, userid, passwd);
}
else
{
throw new Exception(
\nUsage: java Openftch [username password]\n);
}
//Ĭϵ
DefaultContext ctx= new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
Con.setAutoCommit(false);

//ִSQLJӦSQL
try
{
OpF_Curs forUpdateCursor;
String name=null;
Short dept=0;

#sql forUpdateCursor =
{
SELECT name, dept
FROM staff
WHERE job = Mgr
FOR UPDATE OF job
};
//#sql																
while(true)
{
#sql
{
FETCH :forUpdateCursor
INTO :name, :dept
};
//#sql																
if(dept > 40)
{
System.out.println(
name + in dept.
+dept+will be demoted to Clerk);
#sql
{
UPDATE staff SET job = Clerk
WHERE CURRENT OF: forUpdateCursor
};
//#sql																
}
else
{
System.out.println(
name + in dept.
+dept+will be DELETED!);
#sql
{
UPDATE staff SET job = Clerk
WHERE CURRENT OF: forUpdateCursor
};
//#sql																
}
//if-else
}
forUpdatCursor.close();														(5)
}
catch(Exception e)
{
throw e;
finally
{
//ع
System.out.println(\nRollback the transaction );
#sql
{
ROLLBACK;
}
System.out.println(Rollback done.);
}
}
catch(Exception e)
{
System.out.println(e);
}
}
}

10.12 ָӾ䡣

SELECT EMPNAME, SALARY FROM EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 100 ROWS ONLY
OPTIMIZE FOR 20 ROWS

10.13 ͨһ˵αдSQL䡣

EXEC SQL BEGIN COMPOUND ATOMIC STATIC
UPDATE SAVINGS SET
BALANCE =BALANCE-: transfer
WHERE ATMCARD= :atmcard;

UPDATE CHECKING SET
BALANCE =BALANCE +:transfer
WHERE ATMCARD= :atmcard;

INSERT INTO ATMTRANS (TTSTMP, CODE, AMOUNT)
VALUES (CURRENT TIMESTAMP, :code, :transfer);
COMMIT;
END COMPOUND;

10.14

EXEC SQL BEGIN COMPOUND ATOMIC STATIC
UPDATE SAVINGS SET
BALANCE =BALANCE-: transfer
WHERE ATMCARD= :atmcard;

SELECT ACCOUNT INTO: atmcard
WHERE CARDHOLDER= Carl;

UPDATE CHECKING SET BALANCE =BALANCE +:transfer
WHERE ATMCARD= :atmcard;

INSERT INTO ATMTRANS (TTSTMP, CODE, AMOUNT)
VALUES (CURRENT TIMESTAMP, :code, :transfer);

COMMIT;
END COMPOUND;

10.15

EXEC SQL BEGIN COMPOUND NOT ATOMIC STATIC
STOP AFTER FIRST: nbr STATEMENTS
INSERT INTO TAB1 VALUES(:col1val1, :col2val1);
INSERT INTO TAB1 VALUES(:col1val2, :col2val2);
INSERT INTO TAB1 VALUES(:col1val3, :col2val3);
INSERT INTO TAB1 VALUES(:col1val4, :col2val4);
INSERT INTO TAB1 VALUES(:col1val5, :col2val5);
INSERT INTO TAB1 VALUES(:col1val6, :col2val6);
INSERT INTO TAB1 VALUES(:col1val7, :col2val7);
INSERT INTO TAB1 VALUES(:col1val8, :col2val8);
END COMPOUND;

10.16 PREPAREӡ

EXEC SQL PREPARE S1 FROM :stmt;
EXEC SQL PREPARE Q1 INTO :sqlda FROM :query1

10.17 DESCRIBEӡ

EXEC SQL DESCRIBE Q1 INTO :sqlda;

10.18EXECUTEӡ

EXEC SQL EXECUTE S1;
EXEC SQL EXECUTE S2 USING :x, :y, :z;
EXEC SQL EXECUTE S3 USING DESCRIPTOR :sqlda;

10.19EXECUTE IMMEDATEӡ

EXECUTE IMMEDATE :stmt;

10.20 ݿӰʵݿͳƣݿָҪʱɿǹر־һֱܣʱ׼ášODSŻϵͳԴʹáҲӦоܼغŻĹߡʵеĴ洢̿ɴDB2 for z/OSı¼

CREATE PROCEDURE DB2ADMIN.CreateCommandsFor390Parts ()
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
--SQL洢
------------------------------------------------------------------------
P1:BEGIN
DECLARE insertcount,deletecount,updatecount,errorcount,totalcount,
surrogatekey,uncommittedrecords INTEGER DEFAULT 0
DECLARE beginningtimestamp TIMESTAMP
DECLARE commitcount INTEGER DEFAULT 100
DECLARE rowfound CHAR(3)DEFAULT 'yes'
DECLARE PPARTKEY INTEGER
DECLARE PNAME VARCHAR(75)
DECLARE PMFGR CHAR(25)
DECLARE PBRAND CHAR(10)
DECLARE PTYPE CHAR(25)
DECLARE PSIZE INTEGER
DECLARE PCONTAINER CHAR(10)
DECLARE PRETAILPRICE DECIMAL (15,2)
DECLARE PCOMMENT VARCHAR(100)
DECLARE IBMSNAPOPERATION CHAR(1)
DECLARE IBMSNAPLOGMARKER TIMESTAMP
-PART390ıеѭ
DECLARE part390cursor CURSOR WITH HOLD FOR
SELECT
IWH.PART390.PPARTKEY,
IWH.PART390.PNAME,
IWH.PART390.PMFGR,
IWH.PART390.PBRAND,
IWH.PART390.PTYPE,
IWH.PART390.PSIZE,
IWH.PART390.PCONTAINER,
IWH.PART390.PRETAILPRICE,
IWH.PART390.PCOMMENT,
IWH.PART390.IBMSNAPOPERATION,
IWH.PART390.IBMSNAPLOGMARKER
FROM
IWH.PART390
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET rowfound 'no'
SET beginningtimestamp CURRENT TIMESTAMP
OPEN part390cursor
FETCH part390cursor INTO PPARTKEY,PNAME,
PMFGR,PBRAND,PTYPE,
PSIZE,PCONTAINER,
P_RETAILPRICE,P_COMMENT,IBMSNAP_OPERATION,IBMSNAP_LOGMARKER
WHILE rowfound 'yes'DO
CASE IBMSNAPOPERATION
WHEN 'I'
--һ¼ʱʹodspartseq sequenceһ¼
THEN BEGIN
INSERT INTO IWH.PARTLOADCOMMANDS
(
OPERATION,
OPERATIONTIMESTAMP,
ODSPARTKEY,
PARTNAME,
PARTMFGR,
PARTBRAND,
PARTTYPE,
PARTSIZE,
PARTCONTAINER,
PARTRETAILPRICE,
PARTCOMMENT,
PARTSOURCEKEY,
PARTSOURCESYSTEM
)
VALUES
(
IBMSNAPOPERATION,
IBMSNAPLOGMARKER,
--ûΪһֵĸʱ־ʹɸṩʱ־
NEXTVAL FOR ODSPARTSEQ,
PNAME,
PMFGR,
PBRAND,
PTYPE,
PSIZE,
PCONTAINER,
PRETAILPRICE,
PCOMMENT,
PPARTKEY,
'DB7A'
)
SET insertcount insertcount +1
END
WHEN 'U'
--һ¼¼ʱҪҰֵ
THEN BEGIN
SET surrogatekey  (SELECT ODSPARTKEY
FROM IWH.ODSPART
WHERE PARTSOURCEKEY PPARTKEY AND
PARTSOURCESYSTEM 'DB7A')
INSERT INTO IWH.PARTLOADCOMMANDS
(
OPERATION,
OPERATIONTIMESTAMP,
ODSPARTKEY,
PARTNAME,
PARTMFGR,
PARTBRAND,
PARTTYPE,
PARTSIZE,
PARTCONTAINER,
PARTRETAILPRICE,
PARTCOMMENT,
PARTSOURCEKEY,
PARTSOURCESYSTEM
)
VALUES
(
IBMSNAPOPERATION,
IBMSNAPLOGMARKER,
--ûΪһֵĸʱ־ʹɸṩʱ־
surrogatekey,
PNAME,
PMFGR,
PBRAND,
PTYPE,
PSIZE,
PCONTAINER,
PRETAILPRICE,
PCOMMENT,
PPARTKEY,
'DB7A'
)
SET updatecount updatecount +1
END
WHEN 'D'
--һɾ¼ʱҪҰֵ
THEN BEGIN
SET surrogatekey (SELECT ODSPARTKEY FROM IWH.ODSPART
WHERE PARTSOURCEKEY PPARTKEY AND
PARTSOURCESYSTEM 'DB7A')
insert into db2admin.mymessages values (current timestamp,
'surrogate key:
['concat char(surrogatekey)concat ']')
insert into db2admin.mymessages values (current timestamp,
'operation:
['concat ibmsnapoperation concat ']')
INSERT INTO IWH.PARTLOADCOMMANDS
(
OPERATION,
OPERATIONTIMESTAMP,
ODSPARTKEY,
PARTNAME,
PARTMFGR,
PARTBRAND,
PARTTYPE,
PARTSIZE,
PARTCONTAINER,
PARTRETAILPRICE,
PARTCOMMENT,
PARTSOURCEKEY,
PARTSOURCESYSTEM
)
VALUES
(
IBMSNAPOPERATION,
IBMSNAPLOGMARKER,
--ûΪһֵĸʱ־ʹɸṩʱ־
surrogatekey,
PNAME,
PMFGR,
PBRAND,
PTYPE,
PSIZE,
PCONTAINER,
PRETAILPRICE,
PCOMMENT,
PPARTKEY,
'DB7A'
)
SET deletecount deletecount +1
END
ELSE SET errorcount errorcount +1
--ҲѴ¼һܾС
END CASE
DELETE FROM IWH.PART390 WHERE CURRENT OF PART390CURSOR
SET uncommittedrecords uncommittedrecords +1
IF uncommittedrecords >commitcount
THEN
commit
SET uncommittedrecords 0
end if
FETCH part390cursor INTO PPARTKEY,PNAME,
PMFGR,PBRAND,PTYPE, PSIZE,PCONTAINER,
PRETAILPRICE,PCOMMENT,
IBMSNAPOPERATION,IBMSNAPLOGMARKER
END WHILE
--create log entry
INSERT INTO IWH.ODSPARTLOG
(
STEP,
TIMESTAMP,
BEGINTIMESTAMP,
ENDTIMESTAMP,
NUMBERINSERTS,
NUMBERDELETES,
NUMBERUPDATES,
NUMBERERRORS
)
VALUES
(
'Commands for 390 Parts',
CURRENT TIMESTAMP,
beginningtimestamp,
CURRENT TIMESTAMP,
insertcount,
deletecount,
updatecount,
errorcount
)
COMMIT
END P1

10.21 У洢̴Oracleĸļ¼

CREATE PROCEDURE DB2ADMIN.Create_Commands_For_Ora_Parts ( )
RESULT SETS 1
LANGUAGE SQL
--SQL 洢
---------------------------------------------
P1:BEGIN
DECLARE insert_count, delete_count, update_count, error_count, total_count,
surrogate_key, uncommitted_records INTEGER DEFAULT 0
DECLARE beginning_timestamp TIMESTAMP
DECLARE commit_count INTEGER DEFAULT 100
DECLARE row_found CHAR(3) DEFAULT 'yes'
DECLARE P_PARTKEY INTEGER
DECLARE P_NAME VARCHAR(75)
DECLARE P_MFGR CHAR(25)
DECLARE P_BRAND CHAR(10)
DECLARE P_TYPE CHAR(25)
DECLARE P_SIZE INTEGER
DECLARE P_CONTAINER CHAR(10)
DECLARE P_RETAILPRICE DECIMAL (152)
DECLARE P_COMMENT VARCHAR(100)
DECLARE IBMSNAP_OPERATION CHAR(1)
DECLARE IBMSNAP_LOGMARKER TIMESTAMP
--ͨPART_ORA ıеѭ
DECLARE PART_ORA_CURSOR CURSOR WITH HOLD FOR
SELECT
IWH.PART_ORA.P_PARTKEY
IWH.PART_ORA.P_NAME
IWH.PART_ORA.P_MFGR
IWH.PART_ORA.P_BRAND
IWH.PART_ORA.P_TYPE
IWH.PART_ORA.P_SIZE
IWH.PART_ORA.P_CONTAINER
IWH.PART_ORA.P_RETAILPRICE
IWH.PART_ORA.P_COMMENT
IWH.PART_ORA.IBMSNAP_OPERATION
IWH.PART_ORA.IBMSNAP_LOGMARKER
FROM
IWH.PART_ORA
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET row_found 'no'
SET beginning_timestamp CURRENT TIMESTAMP
OPEN PART_ORA_CURSOR
FETCH PART_ORA_CURSOR INTO P_PARTKEY, P_NAME,
P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE,
P_COMMENT, IBMSNAP_OPERATION, IBMSNAP_LOGMARKER
WHILE row_found 'yes'DO
CASE IBMSNAP_OPERATION
WHEN 'I'
--һ¼ʱͨʹods_part_seq һµļֵ
THEN BEGIN
INSERT INTO IWH.PART_LOAD_COMMANDS
(
OPERATION
OPERATION_TIMESTAMP
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
IBMSNAP_OPERATION
IBMSNAP_LOGMARKER
--ݱûиʱ־ʹɸṩʱ־
NEXTVAL FOR ODS_PART_SEQ
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
P_PARTKEY
'ORA'
)
SET insert_count  insert_count +1
END
WHEN 'U'
--һ¼¼ʱҪҰֵ
THEN BEGIN
SET surrogate_key (SELECT ODS_PART_KEY
FROM IWH.ODS_PART
WHERE PART_SOURCE_KEY P_PARTKEY AND
PART_SOURCE_SYSTEM 'ORA')
INSERT INTO IWH.PART_LOAD_COMMANDS
(
OPERATION
OPERATION_TIMESTAMP
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
IBMSNAP_OPERATION
IBMSNAP_LOGMARKER
--ݱûиʱ־ʹɸṩʱ־ֵ
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
P_PARTKEY
'ORA'
)
SET update_count update_count +1
END
WHEN 'D'
--һɾ¼ʱҪҰֵ
THEN BEGIN
SET surrogate_key (SELECT ODS_PART_KEY FROM IWH.ODS_PART WHERE PART_SOURCE_KEY P_PARTKEY AND PART_SOURCE_SYSTEM 'ORA')
insert into db2admin.my_messages values (current timestamp,
'surrogate key:
['concat char(surrogate_key)concat ']')
insert into db2admin.my_messages values (current timestamp,
'operation:['concat ibmsnap_operation concat ']')
INSERT INTO IWH.PART_LOAD_COMMANDS
(
OPERATION
OPERATION_TIMESTAMP
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
IBMSNAP_OPERATION
IBMSNAP_LOGMARKER
--ݱûиʱ־ʹɸṩʱ־ֵ
P_NAME
P_MFGR
P_BRAND
P_TYPE
P_SIZE
P_CONTAINER
P_RETAILPRICE
P_COMMENT
P_PARTKEY
'ORA'
)
SET delete_count delete_count +1
END
ELSE SET error_count error_count +1
--ҲѴ¼ܾС
END CASE
DELETE FROM IWH.PART_ORA WHERE CURRENT OF PART_ORA_CURSOR
SET uncommitted_records uncommitted_records +1;
IF uncommitted_records >commit_count
THEN
commit
SET uncommitted_records 0
end if
FETCH PART_ORA_CURSOR INTO P_PARTKEYP_NAMEP_MFGR
P_BRANDP_TYPE P_SIZEP_CONTAINER
P_RETAILPRICEP_COMMENTIBMSNAP_OPERATION
IBMSNAP_LOGMARKER
END WHILE
--־
INSERT INTO IWH.ODS_PART_LOG
(
STEP
TIMESTAMP
BEGIN_TIMESTAMP
END_TIMESTAMP
NUMBER_INSERTS
NUMBER_DELETES
NUMBER_UPDATES
NUMBER_ERRORS
)
VALUES
(
'Commands for Ora Parts'
CURRENT TIMESTAMP
beginning_timestamp
CURRENT TIMESTAMP
insert_count
delete_count
update_count
error_count
)
COMMIT
END P1

10.22

C:\DB2 VALUES TO_CHAR(CURRENT TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')

10.23

C:\DB2 VALUES TO_DATE('2002-12-19 16:31:05', 'YYYY-MM-DD HH24:MI:SS')

10.24 У洢̴VSAMĸļ¼

CREATE PROCEDURE DB2ADMIN.Create_Commands_For_VSAM_Parts ( ) RESULT SETS 1 LANGUAGE SQL

--SQL 洢

P1:BEGIN
DECLARE insert_countdelete_countupdate_counterror_counttotal_count surrogate_keyuncommitted_records INTEGER DEFAULT 0
DECLARE beginning_timestamp TIMESTAMP
DECLARE row_found CHAR(3)DEFAULT 'yes'
DECLARE commit_count INTEGER DEFAULT 100
DECLARE INPARTKEY INTEGER
DECLARE INPARTNAME VARCHAR(75)
DECLARE INPARTMFGR CHAR(25)
DECLARE INPARTBRAND CHAR(10)
DECLARE INPARTTYPE CHAR(25)
DECLARE INPARTSIZE INTEGER
DECLARE INPARTCONTAINER CHAR(10)
DECLARE INPARTRETAILPRICE DECIMAL (152)
DECLARE INPARTCOMMENT VARCHAR(100)
declare drow_count integer default 0 debug
DECLARE C1 CURSOR WITH HOLD FOR
SELECT
PART_KEYC
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
FROM
DB2ADMIN.VSAM_PARTS
DECLARE CONTINUE HANDLER FOR NOT FOUND
begin Cdebug
insert into db2admin.my_messages values (current timestamp'handler for not found') debug
SET row_found 'no'
enddebug
SET beginning_timestamp CURRENT TIMESTAMP
insert into db2admin.my_messages values (current timestamp'start SP') debug
OPEN C1
FETCH C1 INTO INPARTKEYINPARTNAMEINPARTMFGRINPARTBRAND INPARTTYPEINPARTSIZEINPARTCONTAINERINPARTRETAILPRICEINPARTCOMMENT
--ͨVSAM_PARTS ıеѭ
WHILE row_found 'yes'DO
Insert into db2admin.my_messages values (current timestamp,
char(inpartkey)concat 'inpartkey')--debug
SET surrogate_key (SELECT ODS_PART_KEY
FROM IWH.ODS_PART
WHERE PART_SOURCE_KEY INPARTKEY AND
PART_SOURCE_SYSTEM  'VSAM')
CASE
WHEN surrogate_key is null
-- һ¼ʱͨʹods_part_seqһµļֵ
THEN BEGIN
insert into db2admin.my_messages values (current timestamp'row not found leg of case')
--
INSERT INTO IWH.PART_LOAD_COMMANDS
(
OPERATION
OPERATION_TIMESTAMP
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
'I'
CURRENT TIMESTAMP
--ݱûʱ־ʹõǰʱ䡣
NEXTVAL FOR ODS_PART_SEQ
INPARTNAME
INPARTMFGR
INPARTBRAND
INPARTTYPE
INPARTSIZE
INPARTCONTAINER
INPARTRETAILPRICE
INPARTCOMMENT
INPARTKEY
'VSAM'
)
SET insert_count insert_count +1
END
ELSE BEGIN
insert into db2admin.my_messages values (current timestamp'record found -surrogate_key 'concat char(surrogate_key))--debug
--һ¼¼Ҫʹðֵ
INSERT INTO IWH.PART_LOAD_COMMANDS
(
OPERATION
OPERATION_TIMESTAMP
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
'U'
CURRENT TIMESTAMP
--ݱûʱ־ʹõǰʱ䡣
surrogate_key
INPARTNAME
INPARTMFGR
INPARTBRAND
INPARTTYPE
INPARTSIZE
INPARTCONTAINER
INPARTRETAILPRICE
INPARTCOMMENT
INPARTKEY
'VSAM'
)
SET update_count update_count +1
END
END CASE
--ͨɾȷУjust-processed rowĵط
--ʵǶMQ QueueƻԶȡ
SET uncommitted_records uncommitted_records +1
IF uncommitted_records >commit_count
THEN
commit
SET uncommitted_records 0
end if
FETCH C1 INTO INPARTKEYINPARTNAMEINPARTMFGRINPARTBRANDINPARTTYPEINPARTSIZEINPARTCONTAINERINPARTRETAILPRICEINPARTCOMMENT
END WHILE
--־
INSERT INTO IWH.ODS_PART_LOG
(
STEP
TIMESTAMP
BEGIN_TIMESTAMP
END_TIMESTAMP
NUMBER_INSERTS
NUMBER_DELETES
NUMBER_UPDATES
NUMBER_ERRORS
)
VALUES
(
'Commands for VSAM Parts'
CURRENT TIMESTAMP
beginning_timestamp
CURRENT TIMESTAMP
insert_count
delete_count
update_count
error_count
)
COMMIT
END P1

10.25 У洢ӦODSոļ¼

CREATE PROCEDURE DB2ADMIN.LOAD_ODS_PARTS ( )
LANGUAGE SQL

--SQL 洢

P1:BEGIN
--
DECLARE insert_countdelete_countupdate_counterror_counttotal_count
uncommitted_records INTEGER DEFAULT 0
DECLARE commit_count INTEGER DEFAULT 100
DECLARE beginning_timestamp TIMESTAMP
DECLARE IN_OPERATION CHAR(1)
DECLARE IN_OPERATION_TIMESTAMP TIMESTAMP
DECLARE IN_ODS_PART_KEY INTEGER
DECLARE IN_PART_NAME VARCHAR(75)
DECLARE IN_PART_MFGR CHAR(25)
DECLARE IN_PART_BRAND CHAR(10)
DECLARE IN_PART_TYPE CHAR(25)
DECLARE IN_PART_SIZE INTEGER
DECLARE IN_PART_CONTAINER CHAR(10)
DECLARE IN_PART_RETAIL_PRICE DECIMAL (152)
DECLARE IN_PART_COMMENT VARCHAR(100)
DECLARE IN_PART_SOURCE_KEY INTEGER
DECLARE IN_PART_SOURCE_SYSTEM CHAR(10)
DECLARE row_found CHAR(3)DEFAULT 'yes'
--ָ
DECLARE part_cmd_cursor CURSOR WITH HOLD FOR
SELECT
IWH.PART_LOAD_COMMANDS.OPERATION
IWH.PART_LOAD_COMMANDS.OPERATION_TIMESTAMP
IWH.PART_LOAD_COMMANDS.ODS_PART_KEY
IWH.PART_LOAD_COMMANDS.PART_NAME
IWH.PART_LOAD_COMMANDS.PART_MFGR
IWH.PART_LOAD_COMMANDS.PART_BRAND
IWH.PART_LOAD_COMMANDS.PART_TYPE
IWH.PART_LOAD_COMMANDS.PART_SIZE
IWH.PART_LOAD_COMMANDS.PART_CONTAINER
IWH.PART_LOAD_COMMANDS.PART_RETAIL_PRICE
IWH.PART_LOAD_COMMANDS.PART_COMMENT
IWH.PART_LOAD_COMMANDS.PART_SOURCE_KEY
IWH.PART_LOAD_COMMANDS.PART_SOURCE_SYSTEM
FROM
IWH.PART_LOAD_COMMANDS
DECLARE CONTINUE HANDLER FOR NOT FOUND
Begin
insert into db2admin.my_messages values (current timestamp'entering not found handler')
--debug
SET row_found 'no'
end
insert into db2admin.my_messages values (current timestamp'start load ods sp')debug
SET beginning_timestamp CURRENT TIMESTAMP
OPEN part_cmd_cursor
FETCH part_cmd_cursor INTO IN_OPERATIONIN_OPERATION_TIMESTAMP IN_ODS_PART_KEY
IN_PART_NAMEIN_PART_MFGRIN_PART_BRANDIN_PART_TYPE
IN_PART_SIZE
IN_PART_CONTAINERIN_PART_RETAIL_PRICEIN_PART_COMMENT
IN_PART_SOURCE_KEYIN_PART_SOURCE_SYSTEM
--ͨVSAM_PARTS ıеѭ
WHILE row_found 'yes' DO
insert into db2admin.my_messages values (current timestamp'entering while loop') debug
CASE IN_OPERATION
WHEN 'I'
THEN BEGIN
insert into db2admin.my_messages values ( current timestamp'entering I leg' )debug
INSERT INTO IWH.ODS_PART
(
ODS_PART_KEY
PART_NAME
PART_MFGR
PART_BRAND
PART_TYPE
PART_SIZE
PART_CONTAINER
PART_RETAIL_PRICE
PART_COMMENT
PART_SOURCE_KEY
PART_SOURCE_SYSTEM
)
VALUES
(
IN_ODS_PART_KEY
IN_PART_NAME
IN_PART_MFGR
IN_PART_BRAND
IN_PART_TYPE
IN_PART_SIZE
IN_PART_CONTAINER
IN_PART_RETAIL_PRICE
IN_PART_COMMENT
IN_PART_SOURCE_KEY
IN_PART_SOURCE_SYSTEM
)
SET insert_count insert_count + 1
END
WHEN 'U'
THEN BEGIN
insert into db2admin.my_messages values (current timestamp'entering U leg')debug
UPDATE IWH.ODS_PART
SET PART_NAME IN_PART_NAME
PART_MFGR IN_PART_MFGR
PART_BRAND IN_PART_BRAND
PART_TYPE IN_PART_TYPE
PART_SIZE IN_PART_SIZE
PART_CONTAINER IN_PART_CONTAINER
PART_RETAIL_PRICE IN_PART_RETAIL_PRICE
PART_COMMENT IN_PART_COMMENT
WHERE ODS_PART_KEY IN_ODS_PART_KEY
SET update_count update_count +1
END
WHEN 'D'
THEN BEGIN
insert into db2admin.my_messages values (current timestamp'entering D leg')debug
DELETE FROM IWH.ODS_PART
WHERE ODS_PART_KEY IN_ODS_PART_KEY
SET delete_count delete_count +1
END
ELSE SET error_count error_count +1
END CASE
DELETE FROM IWH.PART_LOAD_COMMANDS
WHERE CURRENT OF part_cmd_cursor
SET uncommitted_records uncommitted_records +1
IF uncommitted_records >commit_count
THEN
commit
SET uncommitted_records 0
end if
FETCH part_cmd_cursor INTO IN_OPERATIONIN_OPERATION_TIMESTAMP
IN_ODS_PART_KEYIN_PART_NAME
IN_PART_MFGRIN_PART_BRANDIN_PART_TYPE
IN_PART_SIZEIN_PART_CONTAINER
IN_PART_RETAIL_PRICEIN_PART_COMMENT
IN_PART_SOURCE_KEY
IN_PART_SOURCE_SYSTEM
END WHILE
--־
INSERT INTO IWH.ODS_PART_LOG
(
STEP
TIMESTAMP
BEGIN_TIMESTAMP
END_TIMESTAMP
NUMBER_INSERTS
NUMBER_DELETES
NUMBER_UPDATES
NUMBER_ERRORS
)
VALUES
(
'Process Part Commands'
CURRENT TIMESTAMP
beginning_timestamp
CURRENT TIMESTAMP
insert_count
delete_count
update_count
error_count
)
COMMIT
END P1

10.28 SQLʵʾαдѯ÷Χɨ衣DB2ͨҪĸЩǷ24ͷ7DB2ֻʹķCOLUMN32ϵNPIȡʾEXPLANУINDEX ACCESSPAGE RANGE SCANȷĶCOLUM32ֵģʹ˲УͿԲдЩּѯʹһҽһ

SELECT  COLUMNS,
FROM PARTITIONED_TABLE
WHERE (COLUMN1 BETWEEN 20000 AND 29999
OR COLUMN1 BETWEEN 40000 AND 49999
OR COLUMN1 BETWEEN 70000 AND 79999)
AND COLUMN2 BETWEEN 2000-09-01 AND 2002-12-25

10.29 DB2Ϊһضʹ÷Χɨ裬Ǽһڶνʡͬظνʱ͵һ巶Χָеνǡ򡱵Ĺϵʾɹ̡

SELECT columns
FROM table
WHERE (clustering-index-column
BETWEEN 2002-09-01 and 2003-3-30
OR clustering-index-of-column
BETWEEN 2002-12-25 and 2003-3-30)

10.30 ͨRUNSTATSռͳϢʵ

RUNSTATS ON TABLE db2admin.department ON ALL COLUMNS

ͬ䡣

RUNSTATS ON TABLE db2admin.department

10.31 ͨRUNSTATSռĿ¼ͳϢʵ

RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname)

10.32 ӽռؼϵͳϢؼбʾɱСûڣռκеͳϢ

RUNSTATS ON TABLE db2admin.department ON KEY COLUMNS

10.33 ͨRUNSTATSռؼϺͷǹؼĿ¼ͳϢʵ

RUNSTATS ON TABLE db2admin.department
ON KEY COLUMNS
AND COLUMNS (deptname)

10.34 ͨRUNSTATSռĿ¼ͳϢʵвֲͳϢ

RUNSTATS ON TABLE db2admin.department AND INDEXES ALL

10.35 ͨRUNSTATSռĿ¼ͳϢԼϸͳϢ˳вֲͳϢ

RUNSTATS ON TABLE db2admin.department AND DETAILED INDEXES ALL

10.36 ͨRUNSTATSֻռ3ָĿ¼ͳϢͳϢʵ

RUNSTATS ON TABLE db2admin.department
FOR INDEXES db2admin.INX1, db2admin.INX2, db2admin.INX3

10.37 ͨRUNSTATSֻռĿ¼ͳϢʵ

RUNSTATS ON TABLE db2admin.department FOR INDEXES ALL

10.38 ͨRUNSTATS WITH DISTRIBUTIONռĿ¼ͳϢʵаֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION AND INDEXES ALL

10.39 ͨRUNSTATS WITH DISTRIBUTIONռĿ¼ͳϢԼϸͳϢʵаֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION AND DETAILED INDEXES ALL

10.40 ͨRUNSTATS WITH DISTRIBUTIONռѡаֲĿ¼ͳϢʵ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON COLUMNS (deptno, deptname)

10.41 ͨRUNSTATS WITH DISTRIBUTIONֻռĿ¼ͳϢʵаdeptnodeptnameϵĻͳϢԼmgrnoadmrdeptϵķֲͳϢ

RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname)
WITH DISTRIBUTION ON COLUMNS (mgrno, admrdept)

10.42 ͨRUNSTATS WITH DISTRIBUTIONռԼаֲĿ¼ͳϢʵ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON KEY COLUMNS
AND COLUMNS (admrdept, location)

10.43 ִ䣬ռֲͳϢĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION AND INDEXES ALL

Уnum_freqvalues parameterΪ10num_quantiles parameterΪ20Ϊûָnum_freqvaluesnum_quantiles

10.44 ִ䣬ռϵĿ¼ͳϢʹָnum_freqvaluesԼݿѡ num_quantilesռϵķֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION DEFAULT
NUM_FREQVALUES 40

10.45 ִ䣬ռϵĿ¼ͳϢdeptnodeptnameϵķֲͳϢΪdeptname÷ֲͳϢķΧdeptnoʹùĬֵһΪIDX1IDX2ռĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON COLUMNS 
(
deptno, deptname
NUM_FREQVALUES 50
NUM_QUANTILES 100
)
DEFAULT
NUM_FREQVALUES 5
NUM_QUANTILES 10
AND INDEXES db2admin.IDX1, db2admin.IDX2

УdeptnameУnum_freqvalues50num_quantiles100deptnoУnum_freqvaluesֵΪ5num_quantilesֵ10
10.46 ִ䣬ռϵĿ¼ͳϢdeptnameϵķֲͳϢδгϵķֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON COLUMNS 
(
deptname
NUM_FREQVALUES 20
NUM_QUANTILES 40
)
AND INDEXES ALL

УdeptnameУnum_freqvalues20num_quantiles40
10.47 ִ䣬ռԼdeptnodeptnameϵĿ¼ͳϢdeptnoеnum_freqvaluesnum_quantilesֵĬֵлá

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON COLUMNS
(
deptno,
deptname
NUM_FREQVALUES 20
NUM_QUANTILES 40
)
DEFAULT
NUM_FREQVALUES 0
NUM_QUANTILES 0
AND INDEXES ALL

УdeptnameУnum_freqvalues20num_quantiles40deptnoУnum_freqvalues0num_quantiles0еУκͳϢ

10.48 ִ䣬ռеĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
ON COLUMNS ((deptno, deptname),
deptname, mrgno, (admrdept, location))

10.49 ִ䣬ռϵĿ¼ͳϢָVARCHARϵLIKEͳϢ

RUNSTATS ON TABLE db2admin.department
ON ALL COLUMNS
and COLUMNS (deptname LIKE STATISTICS)

10.50 ִ䣬ֻעͳϢļռĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
AND INDEXES ALL SET PROFILE ONLY

УRUNSTATSӾ䡰SET PROFILE ONLYռͳϢ
10.51 ִ䣬עͳϢļִ洢ͳϢļRUNSTATSѡռĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET PROFILE

10.52 ִ䣬޸еͳϢļռκĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION AND INDEXES ALL UPDATE PROFILE ONLY

10.53 ִ䣬޸еͳϢļִѸµͳϢļRUNSTATSѡռĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION AND INDEXES ALL UPDATE PROFILE

10.54 ִ䣬ǰעͳϢļѯRUNSTATSѡ

SELECT STATISTICS_PROFILE
FROM SYSIBM.SYSTABLES
WHERE NAME = 'DEPARTMENT'
AND CREATOR = 'DB2ADMIN'

10.55 ִ䣬ͨǰעͳϢļռĿ¼ͳϢ

RUNSTATS ON TABLE db2admin.department USE PROFILE

10.56 ִ䣬ռϵϸĿ¼ͳϢÿĿʹóִϸļ㡣

RUNSTATS ON TABLE db2admin.department
AND SAMPLED DETAILED INDEXES ALL

10.57 ִ䣬ռϵϸͳϢͱķֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION ON KEY COLUMNS
AND SAMPLED DETAILED INDEXES ALL

10.58 ִ䣬ռͳϢ10%ϵķֲͳϢ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION TABLESAMPLE BERNOULLI (10)

10.59 ִ䣬ռͳϢԼظʹͬ

RUNSTATS ON TABLE db2admin.department
WITH DISTRIBUTION TABLESAMPLE
BERNOULLI (10)
REPEATABLE (1024)

10.60 ִ䣬ռ10%ҳϵͳϢͱͳϢֻԱҳгҳУ10%ıҳڱͳϢռͳϢʹеҳ

RUNSTATS ON TABLE db2admin.department
AND INDEXES ALL TABLESAMPLE SYSTEM (10)

10.61 ִ䣬ͨREORGCHKռбĿ¼ͳϢ

REORGCHK UPDATE STATISTICS ON TABLE ALL

10.62 ִ䣬ͨREORGCHKռĿ¼ͳϢ

REORGCHK UPDATE STATISTICS ON TABLE db2admin.department

10.63 ִ䣬ͨREORGCHKռģʽĿ¼ͳϢ

REORGCHK UPDATE STATISTICS ON SCHEMA systools

10.64 ִ䣬ͨLOADSTATISTICS USE PROFILEӾռĿ¼ͳϢ

LOAD FROM inputfile.del OF DEL
REPLACE INTO db2admin.department STATISTICS USE PROFILE

£RUNSTATSڴִ֮еġȻִʱҲռͳϢ⽫ΪռͳϢеһɨ衣˵ʹõһЩѡ
10.65 ִ䣬ռĿ¼ͳϢ

CREATE INDEX db2admin.inx1 ON db2admin.department (deptno)
COLLECT STATISTICS

10.66 ִ䣬ռչĿ¼ͳϢ
CREATE INDEX db2admin.inx2 ON db2admin.department (deptname)
COLLECT DETAILED STATISTICS

10.67 ִ䣬ռչĿ¼ͳϢָʹó
CREATE INDEX db2admin.inx3 ON db2admin.department (deptname)
COLLECT SAMPLED DETAILED STATISTICS

