1 / 33

DB2 Version 7 The complete story?

DB2 Version 7 The complete story?. Issuer : DB-Team 2540 Issued : 2002-02-25. DB2 UDB Server for OS/390 and z/OS Version 7. It started nearly 2 years ago…. In June 2001 we did the first installation in technical test In September 2001 Customer Test (D2VT) was upgraded to V7

thina
Télécharger la présentation

DB2 Version 7 The complete story?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Version 7The complete story? Issuer : DB-Team 2540 Issued : 2002-02-25

  2. DB2 UDB Server for OS/390 and z/OS Version 7

  3. It started nearly 2 years ago…... • In June 2001 we did the first installation in technical test • In September 2001 Customer Test (D2VT) was upgraded to V7 • Few problems during Customer Test. CAE + Vinfo • 18th of November 2001 the subsystems in V201 & V202 was upgraded to V7 • and…...

  4. DB2 Version 7 PROBLEMS!!! • Customer Test. • Class-package of new DB2-client DB2RTC was delayed, old client (CAE V5.2) causes the Connect Server to Stop. • Application abend, fixed by Rebind. • Prod-inst. 18/11. • Lots of abends, DB2 stops, decision to do a Fallback toV6 (19/11). • IBM Fix. • Upgrade of D2BM during a weekend to verify the fix. Still Problems. Fallback to V6 again.

  5. DB2 Version 7 PROBLEMS!!! • The Problem. The error seem to be caused by Plan or Packages where latest bind was done in DB2 V2.3. • Rebind February. Central Rebind was done of all ‘old’ plans & packages. • The 17th of February. All subsystems in Gothenburg up and running on V7

  6. DB2 Version 7 PROBLEMS!!! • DB2RTC. Test of new client and DB2 V7. Char and Varchar field with Local Characters might be truncated if accessed from Office/2K • DB2RTC. Did not work together with Business Object • OTG. Old version of Gateway did not work with V7. Fallback to V6 in D2O1.

  7. DB2 Version 7 PROBLEMS!!! • DB2RTC. Test of new client and DB2 V7. Char and Varchar field with Local Characters might be truncated if accessed from Office/2K • We have added Mr ÅKERBÄRGÖ to our Q.Staff, he helps us to discover codepage-problems. • Now ÅKERBÄRGÖ was shown as ÅKERBÄR in Excel • NAME in STAFF is VARCHAR (9) • 2+1+1+1+1+2+1=9 • Å K E R B Ä R • DISABLEUNICODE=1 as bypass, but set in Connect Server disables all client/applications • Solution in FP7….FP8….FP9?

  8. DB2 Version 7 PROBLEMS!!! • DB2RTC. Did not work together with Business Object • The version of BO(5.1.1) in our Class environment did not have support for V7 FP3 (our DB2RTC level) • New package of BO(5.1.3) scheduled but will take time • Our Site in Gent has been running DB2RTC towards the same level of BO for a while without problems??? • They were running V7 without Fixpacks • Bypass , local install of new BO or server-based reports

  9. DB2 Version 7 PROBLEMS!!! • OTG. Old version of Gateway (V4) did not work with V7. Fallback to V6 in D2O1. • V8.04 installed , still the same problem • While waiting for fix from Oracle we have to run DB2 V6 I 2 subsystems. • No fix will be delivered on V8, wait for 9.2 • Still some problems in 9.2, and the Case tools used must also be certified with v9.2

  10. DB2 Version 7 ”PROBLEMS!!!” • REPEAT USER COLLID EXIT LOCATORS RESTRICT USING COLUMN EXTERNAL LOCK RESULT VALIDPROC COMMENT FENCED LOCKMAX RESULT_SET_LOCATOR VALUES COMMIT FETCH LOCKSIZE RETURN VARIANT CONCAT FIELDPROC LONG RETURNS VCAT CONDITION FINAL LOOP REVOKE VIEW CONNECT FOR MICROSECOND RIGHT VOLUMES CONNECTION FROM MICROSECONDS ROLLBACK WHEN CONSTRAINT FULL MINUTE RUN WHERE CONTAINS FUNCTION MINUTES SAVEPOINT WHILE CONTINUE GENERAL MODIFIES SCHEMA WITH CREATE GENERATED MONTH SCRATCHPAD WLM CURRENT GET MONTHS SECOND YEAR CURRENT_DATE GLOBAL NO SECONDS YEARS CURRENT_LC_CTYPE GO NOT • Harder restriction for Reserved Words gives error at Bind. Enclose Reserved Words within “, Select “NO” Complete list of Reserved Words, please see SQL Reference • Insert into T0T261P.VY_RATTIGHET, SQLCODE=-199 DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD SYSTEM, TOKEN ( SELECT WAS EXPECTED

  11. DB2 Version 7 ”PROBLEMS!!!” • Why ? • Lack of resources • Missing routines for Rebind • Complex environment - Dependencies

  12. Fetch First x Row Only

  13. Online LOAD Resume • + + Availability • SQL applications are not drained • + Ease of use • No need of INSERT programs • + Integrity • Triggers are fired • - Performance • Compared to offline LOAD

  14. DB2 V7 Unicode (UTF8) • H å k a n • EBCDIC • 00800000 0200C8D092819540 40404040 • ASCII • 00800000 020048E56B616E20 20202020 • Unicode • 00800000 020048C3A56B616E 2020202020

  15. Crossload • Maj 2002 • Extern applikation utvecklad mot DB2/W2K skulle verifieras/performance testas I zOS • Data för volyms-test i DB2/w2k , stora volymer – många tabeller –ont om tid Vad gör vi??? • XLOAD var svaret !

  16. Hur används Crossload • Crossload används genom att i en std LOAD utility i z/OS via en cursor peka ut en tabell i ett annat DB2 • Om detta ligger på annan plattform så får man konvertering av datat på köpet • Kolumn-namn måste vara samma eller använd AS //DSNUPROC.SYSIN DD * DECLARE C1 CURSOR FOR SELECT "ID" , "DEPT" , "JOB" , "YEARS" , "SALARY" , "COMM" , "NAME" FROM UDBCONE.Q.STAFF ENDEXEC LOAD DATA INCURSOR C1 RESUME YES LOG YES INTO TABLE STAFF

  17. Crossload, Prepare CDB INSERT INTO SYSIBM.LOCATIONS ( "LOCATION" , "LINKNAME" , "PORT" ) VALUES ('UDBCONE','UDBCONE','50000') ; INSERT INTO SYSIBM.IPNAMES ( "LINKNAME" , "SECURITY_OUT" , "USERNAMES","IPADDR" ) VALUES ('UDBCONE','P','O','131.97.56.42' ) ; INSERT INTO SYSIBM.USERNAMES ( "TYPE" , "AUTHID" , "LINKNAME" ,"NEWAUTHID" , "PASSWORD" ) VALUES ('O','V00DB21','UDBCONE','DB2ADMIN','DB2ADMIN') ;

  18. Bind DSNUTIL For XLOAD Function BIND PACKAGE(DSNUTIL) MEMBER(DSNUGSQL) - ACTION(REPLACE) ISOLATION(CS) ENCODING(EBCDIC) - VALIDATE(BIND) CURRENTDATA(NO) KEEPDYNAMIC(NO) - DBPROTOCOL(DRDA)

  19. Bind Packages For DSNUTIL,SPUFI,DSNTEP2,DSNTIAD In UDB BIND - PACKAGE(UDBCONE.DSNUTIL) MEMBER(DSNUGSQL) ACTION(REPLACE) - ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO) BIND - PACKAGE(UDBCONE.DSNESPCS) MEMBER(DSNESM68) ACTION(REPLACE) - ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND - PACKAGE(UDBCONE.DSNTEP2) MEMBER(DSNTEP2) ACTION(REPLACE) - ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND - PACKAGE(UDBCONE.DSNTIAD) MEMBER(DSNTIAD) ACTION(REPLACE) - ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND - PACKAGE(UDBCONE.CF254) MEMBER(DSNTIAUL) ACTION(REPLACE) - ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO)

  20. REBIND SPUFI,DSNTEP2,DSNTIAD,DSNTIAUL With DBPROTOCOL(DRDA) In z/OS REBIND PACKAGE(DSNESPCS.DSNESM68) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNESPRR.DSNESM68) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNTIAD.DSNTIAD.(*)) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNTEP2.DSNTEP2.(*)) DBPROTOCOL(DRDA) REBIND PACKAGE(CF254.DSNTIAUL.(*)) DBPROTOCOL(DRDA) REBIND PLAN(DSNESPCS) PKLIST(*.DSNESPCS.DSNESM68) DBPROTOCOL(DRDA) REBIND PLAN(DSNESPRR) PKLIST(*.DSNESPRR.DSNESM68) DBPROTOCOL(DRDA) REBIND PLAN(DSNTIAD) PKLIST(*.DSNTIAD.*) DBPROTOCOL(DRDA) REBIND PLAN(DSNTEP2) PKLIST(*.DSNTEP2.*) DBPROTOCOL(DRDA) REBIND PLAN(DSNTIAUL) PKLIST(*.CF254.DSNTIAUL) DBPROTOCOL(DRDA)

  21. Crossload, First try • CDB-definition • Location namn måste vara Databas namn på W2K, Alias fungerar ej • Restriktion , Fel Hur är nuvarande status? • Currentdata(NO) viktigt • Första loaden tog hur lång tid som helst….. • Lägg till FOR FETCH ONLY, rena dunderkuren • Bindade om package med Currentdata(NO) för att erhålla blockning av data • Och hur blev resultatet?

  22. Crossload, Source Tabell SELECT * FROM UDBCONE.Q.STAFF WHERE ID BETWEEN 80 AND 100 ; ---------+---------+---------+---------+---------+---------+---- ID NAME DEPT JOB YEARS SALARY COMM ---------+---------+---------+---------+---------+---------+---- 80 JAMES 20 CLERK ------ 13504.60 128.20 90 KOONITZ 42 SALES 6 18001.75 1386.70 100 PLOTZ 42 MGR 7 18352.80 --------- 99 ÅKERBÄRGÖ 10 MGR 7 18357.50 --------- 91 AÄÖÅABCDE 2540 ÅTEST 12 23.45 23.45 92 ABÖÅABCDE 2540 ÅÄEST 12 23.45 23.45 93 ÅÄÖÅAB 2540 ÅÄÖST 12 23.45 23.45 DSNE610I NUMBER OF ROWS DISPLAYED IS 7 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

  23. Crossload, Resultat Tabell SELECT * FROM STAFF WHERE ID BETWEEN 80 AND 100 ; ---------+---------+---------+---------+---------+---------+---- ID NAME DEPT JOB YEARS SALARY COMM ---------+---------+---------+---------+---------+---------+---- 80 ..... 20 ..... ------ 13504.60 128.20 90 ....... 42 ..... 6 18001.75 1386.70 100 ..... 42 ...@@ 7 18352.80 --------- 99 [....{..| 10 ...@@ 7 18357.50 --------- 91 .{|[..... 2540 [.... 12 23.45 23.45 92 ..|[..... 2540 [{... 12 23.45 23.45 93 [{|[.. 2540 [{|.. 12 23.45 23.45 DSNE610I NUMBER OF ROWS DISPLAYED IS 7 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

  24. Crossload, Hex mot kolumn SELECT ID,HEX(NAME) FROM STAFF WHERE ID BETWEEN 80 AND 100 ; ---------+---------+---------+---------+---------+---------+--------- ID ---------+---------+---------+---------+---------+---------+--------- 80 3F3F3F3F3F 90 3F3F3F3F3F3F3F 100 3F3F3F3F3F 99 B53F3F3F3F433F3FBB 91 3F43BBB53F3F3F3F3F 92 3F3FBBB53F3F3F3F3F 93 B543BBB53F3F DSNE610I NUMBER OF ROWS DISPLAYED IS 7 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

  25. Crossload, Problem • Krävde att sista kolumnen var ett CHAR-fält • Flyttade om ordningen i Select satsen • Lösning finns apar PQ54816 / UQ63417

  26. Crossload, Performance Table MAX REC LENGTH Compress NO.OF RECS TIME Rows/min TAB1 101 Y 45070695 02:52:40 260' TAB2 132 20869642 01:25:08 245' TAB3 132 Y 20869642 01:16:50 271' TAB4 52 15000002 00:38:47 384' TAB5 205 Y 11322252 00:47:49 235' TAB6 329 Y 6001521 00:50:33 120' TAB7 65 5000000 00:13:35 370'

  27. Crossload • December 2002 • Egenutvecklad applikation med utvecklingsmiljö i DB2/W2K vill flytta upp datat till målmiljön på zOS • Nu visste vi svaret – XLOAD ! • Och problemen var ju åtgärdade !?

  28. Crossload, Nya Problem • Date fält fungerar ej • Olika code page – datum presentation? • Nya fel dök upp visade sig ej bero på datum utan återigen var det en bug som var beroende av i vilken ordning kolumnerna låg.

  29. Crossload Summering • En förutsättning för utveckling på Windows plattform med z/OS som tänkt mål-plattform • Bra Performance • Enkelt att använda • Viktigt att definitionerna är samma, tex hade man på windows 4 kolumner på vardera VARCHAR(255) som utgjorde nyckel. Detta fungerade ej på z/OS. Kolumnerna i z/OS definierades som VARCHAR(20) i stället. Detta krävde att substr användes – • SUBSTR(PARTNOPREFIX,1,20) AS PARTNOPREFIX • Andra hållet behövs också, dvs från z/OS till Windows

  30. Restart Light • Nyttjas i vår Data Sharing miljö • Om en lpar går ner startas berörd member upp i annan lpar med Light Option för att släppa lås

  31. Unload Utility • Äntligen en riktig Unload utility!

  32. DB2 Connect • Alternativ ?

More Related