Sunday, March 27, 2011

Peopletools 8.51 : on TABLE’s build

Peopletools 8.51 is coming with a very interesting improvement. The table definition (DDL) is taking from the backend before rebuilding the table. It means that if you have a partitioned table, it will survive a table rebuild, unlike the previous versions which just destroyed them recreating the table as non-partitioned. All the DBA working on Peoplesoft and Oracle have already encountered such situation. Sometimes, inadvertently a very big partitioned table to be rebuild become non-partitioned, raising a lot of subsequent issues.
On an other side, the indexes defined only in database are not (always) taking in account on table’s build. Note that I wrote “always”, it means that under some circumstances it does, have a look.

1. Table’s DDL from database
Firstly, let’s have a try to see how Peoplesoft is taking advantages of the DBMS_METADATA.GET_DDL package available in Oracle database.
Partitioning a table is still not available from AppDesigner, it must be done within the backend. 
First, change a regular table to partition (let’s keep example simplest as possible) :
TableDDL_001 
The table PS_MYTABLE is known from AppDesigner, but even being partitioned, we don’t see it from AppDesigner :
TableDDL_002
TableDDL_003
 TableDDL_004
The DDL here is not coming from the database, but from the AppDesigner definition.
Let’s rebuild the table and see the generated script :
TableDDL_005
Now the script looks much better than ever. For the first time Peoplesoft is able to generated the script with a partitioned table definition as is from the backend :
TableDDL_006
That’ll save a lot of headaches. But overall, do not forget whenever sqlerror exit !

Addendum (28-MAR-2011) : coming across a case where Peopletools destroys the partitions definition, have a look here.

2. Index (shortname) on database only are not taken in account
The table’s DDL is taken from the backend, but if you have DBA in hurry creating indexes only from the backend without using AppDesigner for some performance reason, you’re still facing a known issue when rebuilding table. The database only index will be lost. Let’s create one with a Peoplesoft standard name :
TableDDL_007 
Then rebuild the table (even forcing the index recreation) :
TableDDL_008
The script is as follow :
-- Start the Transaction

-- Data Conversion

-- Create temporary table

CREATE TABLE PSYMYTABLE (EMPLID VARCHAR2(11) NOT NULL,
   MYSTRING VARCHAR2(30) NOT NULL,
   MYNUM DECIMAL(8, 3) NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT"
  PARTITION BY RANGE ("EMPLID")
(PARTITION "PS_MYTABLE_P1"  VALUES LESS THAN ('51')
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT" NOCOMPRESS ,
PARTITION "PS_MYTABLE_P2"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "CUAUDIT" NOCOMPRESS )
/

-- Copy from source to temp table

INSERT INTO PSYMYTABLE (
        EMPLID,
    MYSTRING,
    MYNUM)
  SELECT
        EMPLID,
    MYSTRING,
    MYNUM
  FROM PS_MYTABLE
/

-- CAUTION: Drop Original Table

DROP TABLE PS_MYTABLE
/

-- Rename Table

RENAME PSYMYTABLE TO PS_MYTABLE
/

-- Done
=> As you can see, no index (re)creation !
It is a known case for ages on Peoplesoft. You have to define the same indexes manually onto AppDesigner as it is on database level. However in some cases there’re some bad surprise.

3. Index (longname) on database only are taken in account
Whether a database index is generally not visible from AppDesigner table’s rebuild, it can raise some issue in a specific case. Few weeks ago, I been faced to that case.
Let’s create an index on the the database only, specify a long name (!) :
TableDDL_009 
And now, rebuild the underlying table PS_MYTABLE:
TableDDL_010
=> it will crash, closing the AppDesigner without any message box !
Here below, we can see the log file generated :
SQL Build process began on 27/03/2011 at 18:55:24 for database H91TMPLT.
File: E:\pt851-903-R1-retail\peopletools\src\psbld\bldcreat.cppSQL error. Stmt #: 2180  Error Position: 0  Return: 31603 - ORA-31603: object "PSBMYTABLE_LONG_IN" of type INDEX not found in schema "SYSADM" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 3241 ORA-06512: at "SYS.DBMS_METADATA", line 4812 ORA-06512: at line 1
Failed SQL stmt:SELECT DBMS_METADATA.GET_DDL('INDEX',:1) from dual
There’s no way but rename the index to a shorter name !

Consequently, if a database short named index is not taking in account on table’s build, a long named index is !

Enjoy,

No comments: