![Page 1: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/1.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 1
PeopleTools 8.48/8.49 New Database Features
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk
![Page 2: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/2.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 2
• Oracle Database Specialist– Independent consultant
• System Performance tuning– PeopleSoft ERP
– Oracle RDBMS
• Book– www.psftdba.com
Who Am I?
![Page 3: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/3.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 3
UK Oracle User Group
• UKOUG– PeopleSoft Director
• Annual Conference– Birmingham UK– 1-5 December 2008
• Call for Papers is open– http://conference.ukoug.org
![Page 4: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/4.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 4
Introduction
• The book deals with PeopleTools 8.44.– The presentation is the addenda and errata
• PeopleTools 8.44 was a major release– Instrumentation
• PeopleTools 8.48– ‘Fusion readiness’, integration and messaging,
– some significant database features
• PeopleTools 8.49– New third party versions
![Page 5: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/5.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 5
Agenda
• DDL Models & %UpdateStats
• NOLOGGING
• Column Default Values
• Long Columns, Unicode & CLOBs
• Descending Indexes
• Dirty Read (PT8.49, SQLServer)
![Page 6: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/6.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 6
Resources
• If you can’t hear me say so now.• Please feel free to ask questions as we go along.• The presentation will be available from
– in the conference library– www.go-faster.co.uk
• Notes pages include references to my blog– blog.psftdba.com
• Errata and Addenda for the book can be found at www.psftdba.com
![Page 7: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/7.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 7
%UpdateStats
• Genuine challenge in any RDBMS with a Cost-base optimizer– What do I do about statistics on a working
storage table?
• Platform generic solution:– Collect statistics in the program after you
populate the table.
![Page 8: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/8.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 8
DDL Models
• From PT8.x, on Oracle, %UpdateStats uses two new DDL models – Up to PT8.47
ANALYZE TABLE [TBNAME] COMPUTE STATISTICS;
ANALYZE TABLE [TBNAME] ESTIMATE STATISTICS;• Probably due to support for Oracle 8i up to PT8.44.
– From PT8.48Uses DBMS_STATSSilly Defaults
![Page 9: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/9.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 9
Delivered DDL Model
• Estimate– Model 4
DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=>1,method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);
• Compute– Model 5
DBMS_STATS.GATHER_TABLE_STATS (ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=> dbms_stats.auto_sample_size,method_opt=> 'FOR ALL INDEXED COLUMNS
SIZE 1',cascade=>TRUE);
![Page 10: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/10.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 10
Delivered Models
Estimate and Compute Confused Saved by 1% Sample Size for Compute
1% Sample Size for Compute– I think that’s a typo!
DBMS_STATS.AUTO_SAMPLE_SIZE– Default in 10g. Good idea. Most of the time most people use an
unnecessarily large sample size. SIZE 1
– Oracle 9i default, removes histograms.– 10g default is FOR ALL COLUMNS SIZE AUTO
ALL INDEXED COLUMNS SIZE 1– Removes histograms from indexed columns– Leaves old histograms on unindexed columns
![Page 11: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/11.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 11
My suggested DDL Model
• Estimate– Model 4
DBMS_STATS.GATHER_TABLE_STATS (ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=> dbms_stats.auto_sample_size,method_opt=> 'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
• Compute– Model 5
DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME],tabname=>[TBNAME],estimate_percent=>100,method_opt=> 'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
![Page 12: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/12.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 12
Optimizer Dynamic Sampling
• Oracle’s approach to same problem of statistics working storage tables– Delete the statistics– Have the database sample a few blocks at parse
time.• JIT ANALYZE• Also effective with Global Temporary Tables
– From 10g: Lock the statistics• OPTIMIZER_DYNAMIC_SAMPLING = 4
![Page 13: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/13.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 13
PeopleSoft workaround to handle locked statistics
• DBMS_STATS.GATHER_TABLE_STATS raises ORA-20005 on table with locked statistics.
• %UpdateStats in Application Engine
• Also in COBOL
– Need a PL/SQL procedure to encapsulate DBMS_STATS
– Handle error in exception.
– Call PL/SQL procedure in DDL model.• See
http://blog.psftdba.com/2007/05/updatestats-v-optimizer-dynamic.html.
![Page 14: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/14.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 14
NOLOGGING operations
• Not written to the redo stream.– Operations do not appear in a Data Guard standby
database• You will get corrupt blocks
ORA-01578: ORACLE data block corrupted
(file # 1, block # 134419)
ORA-01110: data file 1: '/u01/oradata/hcm89/system01.dbf‘
ORA-26040: Data block was loaded using the NOLOGGING option
– Cannot recover objects on restore• reapply archive logs after restoring backup
![Page 15: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/15.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 15
NOLOGGING in PeopleSoft
• From PeopleTools 8.48:• Application Designer
– builds all indexes PARALLEL NOLOGGING– Then alters them NOPARALLEL LOGGING
Can remove NOLOGGING from DDL model
Alter statement is hard coded,not in the DDL model
![Page 16: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/16.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 16
NOLOGGING in PeopleSoft
Good idea in non-production environments.– Probably not in archive log mode anyway– Better index build performance
Bad in Production– Corrupt Blocks
Simple WorkaroundALTER DATABASE FORCE LOGGING;
![Page 17: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/17.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 17
Column Default Values
• Most columns in PeopleSoft are NOT NULLORA-01400: cannot insert NULL into
("SYSADM"."table name"."field name")
• When a new column is added to a table– Need to visit all insert statements.
• Unless I could do this:DESCR VARCHAR2(30) DEFAULT ' ' NOT NULL,SEX VARCHAR2(1) DEFAULT 'U' NOT NULL,
![Page 18: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/18.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 18
• Record Properties
• And then fields defaults appear in create table DDL.– Some of them.
Mobile Synchronisation Framework
![Page 19: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/19.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 19
CREATE TABLE PS_DMK (EMPLID VARCHAR2(11) DEFAULT ' ' NOT NULL,EFFDT DATE,SEX VARCHAR2(1) DEFAULT 'U' NOT NULL,ACTION_DT DATE NOT NULL,ARRIVAL_TIME DATE NOT NULL,ACTION_DTTM DATE,AA_PLAN_YR SMALLINT DEFAULT 42 NOT NULL,ACCRUED_AMOUNT DECIMAL(15, 2) DEFAULT 0 NOT NULL) …
![Page 20: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/20.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 20
Column Default Values
Only character and numeric literal defaults used as columns defaults.Dates ignored.
• What I was hoping for was…
ACTION_DT DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
ARRIVAL_TIME DATE DEFAULT SYSDATE NOT NULL,
…
![Page 21: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/21.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 21
Long Columns & CLOBs
• Previously– Long Characters Columns become LONG
columns
• Difficult to handle– Can’t use character functions– Trouble using across database links– Deprecated by Oracle RDBMS
![Page 22: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/22.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 22
Long Columns and CLOBs
• From PeopleTools 8.48:If PSSTATUS.DATABASE_OPTION = 2
– Long characters become CLOBs– Raw fields become BLOBs
Supported from Apps 9.0 only– Why not legacy apps on PT8.48?
![Page 23: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/23.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 23
Unicode
• Uses Character Semantics
• Reduces parse problem
… EMPLID VARCHAR2(11 CHAR)…– Except that the ‘char’ is assumed
![Page 24: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/24.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 24
How did PeopleSoft used to create character columns?
• Byte semantics– 3 bytes / character
CREATE TABLE PS_JOB
(EMPLID VARCHAR2(33)CHECK(LENGTH(EMPLID)<=11)
…
);
![Page 25: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/25.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 25
So...
• Length checking constraint on EVERY character column in the database!
• >500,000 user constraints in a Financials database
• What effect does this have on performance?
![Page 26: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/26.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 26
Experiment
create table test_nocons(id number,field_01 varchar2(30)…,field_20 varchar2(30));
create table test_nocons_char(id number,field_01 varchar2(30 char)…,field_20 varchar2(30 char));
create table test_cons(id number,field_01 varchar2(90)
CHECK(LENGTH(field_01)<=30)
…,field_20 varchar2(90)
CHECK(LENGTH(field_01)<=30)
);
![Page 27: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/27.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 27
Experiment 1: Populate tables only one hard parse
• Only one hard parse
BEGIN FOR i IN 1..10000 LOOP INSERT INTO test_nocons VALUES (i
,RPAD(TO_CHAR(i),11,'.')…
,RPAD(TO_CHAR(i),30,'.'));COMMIT;
END LOOP;END;/
![Page 28: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/28.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 28
Results of Experiment 1
• Insert 10000 rows
• Elapsed time:– No constraints: 5.36s– With constraints: 5.79s– Character semantics: 5.11s
![Page 29: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/29.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 29
Experiment 2
• Now deliberately generate different SQL statements, forcing parse every time.
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE IMMEDIATE 'INSERT INTO test_nocons VALUES ('||i||',RPAD(TO_CHAR('||i||'),11,''.''))';
END LOOP;
COMMIT;
END;
/
![Page 30: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/30.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 30
Results of Experiment 2
• >90% parse time
• Elapsed Time:– Without Constraints: 14.71s– With Constraints: 37.01s– Character Semantics: 11.57s
![Page 31: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/31.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 32
Conclusion
• Execution of constraints adds overhead, but not a huge amount.
• Parse overhead of constraints is very severe.– Tracing shows that they keep scanning cons$
Character semantics are a good thingThey even seem to work better than byte
semantics without constraints!
![Page 32: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/32.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 33
But there is a catch!
• Like CLOBs this feature is controlled by– PSSTATUS.DATABASE_OPTION = 2
Only supported from Applications 9.
![Page 33: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/33.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 34
Descending Indexes
SELECT EMPLID,...FROM PS_JOBWHERE EMPLID=:1AND EMPL_RCD=:2ORDER BY EMPLID, EMPL_RCD, EFFDT DESC, EFFSEQ DESC
![Page 34: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/34.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 35
Descending Indexes
• Removed in PeopleTools 8.14/8.15– Bug 869177 in Oracle 8i– ORA-03113
• Reintroduced in PeopleTools 8.48
• Increase in performance of effective date/sequence queues. – Usually ask for max or current (max <= today)– Search from latest record back instead of earliest record forwards– Can be lots of history and not much future
• Often current is latest
– Don’t have to scan all of history
![Page 35: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/35.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 36
Descending Index
• Form of Function Based Index– Hidden Column– System Generated Name
• Eg. SYS_NC00164$
• appears in USER_IND_COLUMNS
– USER_IND_EXPRESSIONS• Translation of column to expression
![Page 36: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/36.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 37
Dirty Read PT8.49, PSQRYSRV on SQLServer
• Oracle doesn’t do dirty reads because it has multi-versioning supported by Undo (Rollback) Segments– SQL Server 2000 provided read committed
mode by locking queried data– SQL Server 2005 has multi-versioning option
(similar effect to Oracle)
• If you have upgraded to PT8.49 why haven’t you upgraded to SQL Server 2005?
![Page 37: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/37.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 38
Questions?
![Page 38: Collaborate08 PeopleTools 8.48/9© PeopleTools 8.48/8.49 New Database Features David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081518/551bc480550346af588b4cfc/html5/thumbnails/38.jpg)
Collaborate08 PeopleTools 8.48/9
© www.go-faster.co.uk 39
PeopleTools 8.48/8.49 New Database Features
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk