On The Peoplesoft Road: 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) :

The table PS_MYTABLE is known from AppDesigner, but even being partitioned, we don’t see it from AppDesigner :

The DDL here is not coming from the database, but from the AppDesigner definition.
Let’s rebuild the table and see the generated script :

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 :

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 :

Then rebuild the table (even forcing the index recreation) :

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 (!) :

And now, rebuild the underlying table PS_MYTABLE:

=> 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 !