i5
1. DB Configuration Library vs. Collection DB CRTLIB CL : SQL interface SQL
1. DB Configuration Library vs. Collection
Notes (Collection) create collection SQL command, iseries Navigator. : DB catalog views. table, commitment control., CRTLIB table,. r530 SQL table PF. QDFTJRN data area : http://www.redbooks.ibm.com/abstracts/tips0604.html?open
1. DB Configuration Catalog Views iseries catalog tables views : QSYS and QSYS2 ODBC JDBC catalog views : SYSIBM Catalog Views in each Collection
1. DB Configuration application select 70% Data validation check DDS : Read check DDL : Write check Select application? DDL modernization
Notes SQL DDS read write. SQL DDS, read. SQL table insert, storage. CHGPF FILE(lib/table1) SIZE(125000 1000 3) ALLOCATE(*YES) CHGPF, a CLRPFM RGZPFM storage allocation.
2. Journal Configuration Program New Row Put File A Journal File A: PT New Row 2 1 New Row File A Journal Receiver's content PT: New Row PT New Row Jrn Rcvr
Notes,. DSPJRN. PT.,. DB log SQL application., OLTP DB,.
2. Journal Configuration -Journal Caching Batch Application ODP Buffer PF Cacheenabled CHGJRN... JrnCache(*Yes) Journal Main memory cache 128K Buffer Receiver 757 Meg IOP 128K Buffer One per disk arm Write Cache System ASP User ASP AP PF
2. Journal Configuration Batch Job 5 Million DB operations (10% Adds, 90% Updates) 9 Million resulting Journal entries (captured both before and after images) Elapsed Time Original Batch run, no Journaling 1118 Sec Ordinary Journaling enabled 9773 Sec Using the new Journal cache option 1433 Sec 6 A priced feature of the Operating System ƒ Option 42 ƒ CHGJRN JRN(MYLIB/MYJRN) JRNCACHE(*YES)
Notes write, SQL, 128K write block DISK I/O. CHGJRN enable., product.
2. Journal Configuration Commitment Control ƒcommitment control entry pair BC - Begin Commitment Control and EC - End Commitment Control bound all transactions for a Job SC and CM (or RB) serve as bookends for a particular transaction Example Application: STRCMTCTL Transaction 1 Transaction 2 Transaction 3 ENDCMTCTL......... BC SC......... CM SC CM SC RB EC Commit Rollback
2. Journal Configuration Determining Rollback Process, WRKCMTDFN + 5 + F6 + Journal Display Journal Status Job: QPADEV001P User: JOEUSER Number: 071957 Commitment definition........ : *DFTACTGRP Type options, press Enter. 5=Display commit cycle entries 6=Display all entries 7=Work with journal attributes Commit Cycle Opt Journal Library Identifier MYJRN1 MYLIB1 123 MYJRN2 MYLIB2 456 _ MYJRN3 MYLIB3 789 System: ISERIES1 New F3=Exit F5=Refresh F6=Display resource status F9=Command line Bottom F11=Display rollback status F12=Cancel F23=More options
2. Journal Configuration Determining Rollback Process F11 : Display Journal Status System: RCHASJMB Job: QPADEV001P User: RANDYJ Number: 071957 Commitment definition........ : *DFTACTGRP RollBack Type options, press Enter. 5=Display commit cycle entries 6=Display all entries 7=Work with journal attributes -----------Rollback----------- Opt Journal Library Date Time % Complete _ MYJRN1 MYLIB1 100 _ MYJRN2 MYLIB2 08/23/05 12:00:00 50 _ MYJRN3 MYLIB3 0 F3=Exit F5=Refresh F6=Display resource status F9=Command line F11=Display unlock status F12=Cancel F23=More options Bottom
2. Journal Configuration Determining Rollback Process F11 : System: RCHASJMB Display Journal Status Job: QPADEV001P User: RANDYJ Number: 071957 Commitment definition........ : *DFTACTGRP Type options, press Enter. 5=Display commit cycle entries 6=Display all entries 7=Work with journal attributes Helps track time spent unlocking lots of Rows ------------Unlock------------- Opt Journal Library Date Time % Complete _ MYJRN1 MYLIB1 100 _ MYJRN2 MYLIB2 08/23/05 10:42:00 96 _ MYJRN3 MYLIB3 0 F3=Exit F5=Refresh F6=Display resource status F9=Command line Bottom
2. Journal Configuration Open (OP) Close (CL) Not required for: IPL recovery APYJRNCHG / RMVJRNCHG High Availablity Business Partners... OP UB UP... DL CL... Open entry Close Entry CHGJRNOBJ Obj((*ALL *FILE)) Atr(*OMTJRNE) OmtJrnE(*OPNCLOSYN)
2. Journal Configuration "Before" Before image RmvJrnChg RmvJrnChg Scan for "before" images... UB UP... DL PT... DL UB UP... "Before"image "Before"image CHGJRNOBJ Obj((*ALL *FILE)) Atr(*IMAGES) Images(*AFTER)
Notes. table open close.,. RmvJrnChg, RmvJrn,.,.
2. Journal Configuration Access Path CHGJRN JRN(MYLIB/MYJRN) RCVSIZOPT(*MAXOPT2 *RMVINTENT) JRNRCV(*GEN) IOP 1 Main Memory AP Journal Entry PF Journal Entry SMAPP-induced traffic IOP 2 Write Cache Write Cache User ASP PF JOE... AP JOE storage is recycled! Entries are NOT remote journaled, nor written to tape
Notes *RMVINTENT,, IPL keyed access path integrity hidden., hidden. *RMVINTENT SMAPP, STRJRNAP. SMAPP System Managed Access Path abnormal IPL system access path protect. *RMVINTENT. DSPJRN JRN(LIB/JRN) OUTPUT(*OUTFILE) INCHIDENT(*YES) OUTFILEFMT(*TYPE5) OUTFILE(LIB/OUTFILE) SELECT SUM(JOENTL) FROM LIB/OUTFILE WHERE JOCODE = I *RMVINTENT CHGJRN CHGJRN JRN(LIB/JRN) JRNRCV(*GEN) RCVSIZOPT(*RMVINTENT) *RMVINTENT hidden entry tape, tape., SAVOBJ.
3. Language Configuration Language Character() vs. glyph() Character Sample glyphs Character vs. Code point : NLS National Language File Table binary data NLS binary data PF Table Attribute Default CCSID Current job Default CCSID PF creation PF table CCSID Job CCSID 65535, Default ccsid job ccsid Job CCSID 65535 (OS400 default), LangID (User profile ) CRTPF CRTPF CL command job ccsid., job ccsid 65535 PF ccsid 65535 OS400, I5/Os system default ccsid 65535 CCSID Server character encoding
3. Language Configuration CCSID SBCS : 256 code point DBCS : 65535 code point Unicode : over 1 milion code point Encoding schema ASCII : Intel base flatform EBCDIC : IBM Unicode : 1,000,000 character Defining code page I5/iSeries field ccsid multi language flat file ccsid Review current level code page DSPJOB OPTION(*DFNA) Example Language identifier............... : KOR Country or region identifier.......... : KR Coded character set identifier......... : 65535 Default coded character set identifier..... : 933 Review current level code page Field ccsid => Unique in iseries/i5 File ccsid Current job ccsid
Notes i5, CCSID, Code Page.., client/server server client code page DBCS. client window code page dos chcp. code page 949. client 949 server 933. conversion 949 client 933., 937., server client 933. 00949 00833, 00933, 01208, 01364, 13121, 13488, 61952 OS code page. CCSID Windows Operating System 1250 Eastern European 1251 Cyrillic 1252 US (ANSI) 1253 Greek 1254 Turkish 1255 Hebrew 1256 Arabic 874 Thai 932 Japan 936 Simplified Chinese (PRC, Singapore) 949 Korean 950 Chinese (Taiwan, Hong Kong)
3. Language Configuration OS/400 Primary language feature code QCCSID 65535 default system environment CCSID Encoding Scheme, Character Set, Code Page 1100=EBCDIC single-byte 1301=EBCDIC mixed-byte (single+double)
3. Language Configuration Language Related System Values and Default Values
3. Language Configuration
Notes DB, IBM EBCDIC., CCSID. RPG, Cobol application EBCDIC, DB application. ODBC interface window client code page conversion table ascii. Java ebcdic unicode.,.. ccsid 933, client., PC client,. ccsid 937 935 code point ccsid 933 ccsid 933 code point client,., Global Unicode.
3. Language Configuration Korean language Table CCSID : table 933 A-type, table ccsid 833 : ccsid 933 SBCS part 833 (default) J-type(DDS) G-type(DDS, SQL) (DBCS) (SBCS) : O-type J-type O-type CCSID 933, x 0e x 0f G-type default CCSID 834 : CCSID 933 DBCS part x 0e x 0f G-type CCSID unicode : USC-2, UTF-16 x 0e, x 0f : mixed type DBCS ebcdic character G-type O-type x 0e x 0f
3. Language Configuration Data conversion between systems Client Access ODBC : National Language Support Consideration Conversion R450 and Earlier Multi-step operation => Data -> job -> Ansi code page(round trip conversion) R510 and Later Direct Conversion Conversion is done on the client ODBC Spec Unicode ODBC 2.5 Non-support ODBC 3.x Support
3. Language Configuration Data conversion between systems Client to DB2 Connect Gateway to DB2 UDB server conversion Client to DB2 Connect Gateway to DB2 UDB server conversion Conversion Requester Receiver.
4. Unicode Unicode?...... code page mapping.. unicode. Unicode coding mapping.
4. Unicode DBCS Unicode? IBM Unicode EBCDIC Unicode : Amharic (a native Ethiopian language) Unicode Unicode... DBCS. DBCS 4 :,, Simplified Chinese, Traditional Chinese DBCS CCSID DBCS Code CCSID, CCSID.
4. Unicode Unicode UTF-8: Unicode Transformation Format, ASCII 8-bit encoding, character data type. A UTF-8 1,2,3 or 4 bytes. Name : CHAR(10) CCSID(1208) UCS-2: Universal Character Set, 16 bit graphic data type. UCS-2 UTF-16 UTF-16. UCS-2 UTF-16, UTF-16. Name : GRAPHIC(10) CCSID(13488) UTF-16: Unicode Transformation Format, 16bit encoding 100 Graphic. Name : GRAPHIC(10) CCSID(1200) UTF-16, UTF-8 UCS Encoding
4. Unicode Globalization
4. Unicode Unicode data type? iseries Data Transfer : FTP : i5/os FTP Unicode. i5/os FTP cannot correctly perform data transfers where the file or directory data send on the network is in UCS-2 or UTF-16 formats (CCSIDs 13488, 61952, or 1200). The i5/os FTP design properly supports UTF-8 data (CCSID 1208), however, IBM has not formally tested this support, and cannot guarantee that it will work in all cases. EBCDIC UNICODE? SBCS : CCSID 833 unicode DBCS CCSID 933 unicode, EBCDIC DBCS data x 0e, x 0f pair conversion error (SQL0330) migration x 0e, x 0f pair
5. DB General Function
5. DB
Notes Datalink LOB type non-traditional data., datalink SQL table LOB. LOB. Data Object database transaction LOB data UDF one-step Substr, Concat SQL function Datalink. application object LOB limit 15MB
5. DB Varchar Type (VARCHAR/VARGRAPHIC), VARCHAR ALLOCATE(0), 90-95% overflow storage area, VARCHAR wildcard,
Notes ALLOCATE storage. CREATE TABLE dept ( id CHAR(4), name VARCHAR(40), bldg_num INTEGER ) CREATE TABLE dept ( id CHAR(4), name VARCHAR(40) ALLOCATE(40), bldg_num INTEGER ) Fixed Length Primary Storage Variable Length Auxilary Storage Fixed & "Variable" Length Storage 05 SALES
Notes Oracle mapping
5. DB Null null null (nonnull) (absence)., Not Null special register null. COUNT COUNT_BIG null. ROWID null null. Null => Oracle:...NVL(MANAGER_ID, 'No Manager')... => DB2 UDB:...COALESCE(MANAGER_ID, 'No Manager')...
5. DB Index Maintenance serial update., index update. maintenance, DB2 SMP concurrent process. Parallel index maintenance I/O resource Tip:, 20%, index drop parallel processing index rebuild.
6. Client interface ODBC Setting : iseries Data Access component ODBC Data Source application, microsoft office Data Source DSN User DSN : window log-in current user System DSN : system user system DSN, data source File DNS : ODBC driver setting file, DSN Performance option Lazy close : application file open/close open connection (Application open/close, Access performance ) Enable Pre-Fetch of Data for Queries : Select, open fetch operation first block of data ( ) Enable Data Compression : client decompression CPU, client server PC CPU Use Blocking with a Fetch of One row : ODBC driver result set fetch multiple records fetch request trip fetch rows Record Blocking : iseries server ODBC client blocking size ( rows ) Query Optimization Goal : r540 optimization Default : Extended dynamic full query(*allio), first block(*firstio) First Block(*FIRSTIO) : return block of data optimize Full Query(*ALLIO) : rows optimize Extended dynamic : Access plan SQLPKG
6. Client interface OLE DB ODBC data source application properties Setting the iseries Access OLE DB Provider properties (ADO connection example) 'Create a connection object Dim cn as ADODB.Connection Set cn = New ADODB.Connection 'Use the iseries Access Provider cnas400.provider = "IBMDA400" ' Set the OLE DB Performance properties cnas400.properties("block Fetch") = True => r530 cnas400.properties("catalog Library List") = "MyLib1, MyLib2" => r510, Special values(*usrlibl, *ALL) cnas400.properties("data Compression") = True => r530 cnas400.properties( Query Optimization Goal") = 0 => r530 (0 : *ALLIO if extended dynamic is used, otherwise, *FIRSTIO, 1 : *FIRSTIO, 2 : *ALLIO) cnas400.properties("use SQL Packages") = True => r530 cnas400.properties("sql Package Name") = "MyAppName" => 7 + 3 cnas400.properties("sql Package Library") = "QGPL" cnas400.properties("add statements to SQL Package") = True cnas400.properties("unusable SQL Package Action") = 0 'Open the connection cnas400.open "Data Source=MyiSeries;", "MyUserID", "MyPWD"
6. Client interface JDBC JDBC 2.0 API : java.sql and javax.sql JDBC architecture : JDBC API interfaces classes, driver, database Driver : Java application database, Java language DB specific language mapping Type 1 Client ODBC libraries Type 2 Client DB specific libraries Type 3 Driver passes calls to proxy server Type 4 Driver database
6. Client interface JDBC driver Java Performance :, type 3 4 driver (connection pool & caching) In i5/iseries : Type 2 driver (WAS application DB server )
6. Client interface Transaction Transaction statement java.sql.connection interface transaction public interface Connection { boolean getautocommit(); void setautocommit(boolean autocommit); void commit(); void rollback(); } JDBD AutoCommit mode => commit() => commit. Batch transaction, AutoCommit mode false try{ connection.setautocommit(fase); }
6. Client interface isolation level Isolation level dirty ready, phantom reads non-repeatable reads Java.sql.connection interface isolation level method public interface Connection { public static final int TRANSACTION_NONE = 0 public static final int TRANSACTION_READ_UNCOMMITTED = 1 public static final int TRANSACTION_READ_COMMITTED = 2 public static final int TRANSACTION_REPEATABLE_READ = 4 public static final int TRANSACTION_SERIALIZABLE = 8 } Isolation level description Transaction Level Dirty reads Permitted Phenomena Non Repeatable reads Phantom reads TRANSACTION_NONE N/A N/A N/A TRANSACTION_READ_UNCOMMITED YES YES YES TRANSACTION_READ_COMMITED NO YES YES TRANSACTION_REPEATABLE_READ NO NO YES TRANSACTION_SERIALIZABLE NO NO NO
6. Client interface isolation level Concurrent, TRANSACTION_NONE Default isolation level TRANSACTION_READ_UNCOMMITED Isolation level method -int -void gettransactionisolation(); settransactionisolation(int isolationlevelconstant); Isolation level Mapping in i5/iseries server Transaction Level On iseries/i5 Commit mode Performance impact TRANSACTION_NONE *NC (No Commit) COMMIT(*NONE) FASTEST TRANSACTION_READ_UNCOMMITED *UR (Uncommitted Read) COMMIT(*CHG) FASTEST TRANSACTION_READ_COMMITED *CS (Cursor Stability) COMMIT(*CS) FAST TRANSACTION_REPEATABLE_READ *RS (Read Stability) COMMIT(*ALL) or COMMIT(*RS) MEDIUM TRANSACTION_SERIALIZABLE *RR (Repeatable Read) COMMIT(*RR) SLOW
Notes Dirty read problem Transaction 1 price 10(step 9), transaction 2 isolation level uncommitted read step 6 20 transaction data integrity T1 transaction T2 data integrity T2 T1 transaction price, isolation level TRANSACTION_READ_COMMITED TRANSACTION_READ_COMMITED data integrity, lock level performance
Notes Unrepeatable read problem Transaction 1 price 10, transaction 2 price 20 commit, transaction 1 price transaction 20 data integrity transaction data integrity isolation level TRANSACTION_REPEATABLE_READ TRANSACTION_REPEATABLE_READ data integrity, lock level performance
Notes Phantom read problem Transaction 1 company_id 10 1, transaction 2(step7) row insert commit, transaction transaction 1 2 transaction row isolation level TRANSACTION_SERIALIZABLE TRANSACTION_SERIALIZABLE lock level data integrity, performance
6. Client interface Connection Pool Application DB connection resource performance, DB server application performance Connection pool connection open min/max connection incremental size properties / Application server JDBC 2.0 ConnectioPoolDataSource PooledConnection i5/iseries DB connection QZDASOINIT pre-started job WAS data source connection pool, QZDASOINIT PJ end user web browser, connection, active pool return Connection pool QZDASOINIT job end user connection, active PSRW (Program Start Request Wait) (Recycle).
6. Client interface Extended Dynamic Support (SQL package) Extended dynamic SQL access plan memory physical object, job
6. Client interface Extended Dynamic Support (SQL package) Toolbox for iseries(jt400.jar or Jtopen) ODBC default extended dynamic support, coding enable
7. Stored Procedure Isolation Level (Commit mode) iseries Navigator (GUI) Default No Commit mode Run SQL Script SQL Script JDBC setup commit mode PRTSQLINF For example CREATE PROCEDURE LEELIB.SAVETEST4 ( ) LANGUAGE SQL SPECIFIC LEELIB.SAVETEST4 NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT BEGIN INSERT INTO LEELIB. TEST VALUES ( 'B' ) ; ROLLBACK ; END ;
7. Stored Procedure iseries Navigator
7. Stored Procedure Run SQL Script commit mode *CHG
7. Stored Procedure Interface commit mode CREATE PROCEDURE LEELIB.SAVETEST4 ( ) LANGUAGE SQL SPECIFIC LEELIB.SAVETEST4 NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT SET OPTION COMMIT=*CHG BEGIN INSERT INTO LEELIB. TEST VALUES ( 'B' ) ; ROLLBACK ; END ;
8. References Manual SQL Reference, SQL Programming from Info Center http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/pdf_table/index.htm RedBooks Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iseries, SG24-6503-01 Preparing for and Tuning the V5R2 SQL Query Engine on DB2 Universal Database for iseries, SG24-6598-00 SQL Performance Diagnosis on IBM DB2 Universal Database for iseries, SG24-6654-00 http://www.redbooks.ibm.com