Data Type Changes for Oracle/Relscripts/Database_OPTIONSThere are few Data types changed in Peoplesoft for Oracle Platform, which plays a crucial role in upgrades, as well as installation.
From App9 on wards Peoplesoft started supporting CLOB and BLOB. CLOBS replaced LONG datatype and BLOBS replaced LONG ROW in Peoplesoft. CLS (Character Length semantic) was another feature introduced in Apps9.
From App9 on wards Peoplesoft started supporting CLOB and BLOB. CLOBS replaced LONG datatype and BLOBS replaced LONG ROW in Peoplesoft. CLS (Character Length semantic) was another feature introduced in Apps9.
What is CLS?
CLS is a feature introduced in Apps9 and above and is supported only for Unicode Oracle DBs. Before Apps9 to support complex character sets such as Chinese, Peoplesoft used a method called tripling feature. This means a field say FIELD1 in appdesigner which is of type CHARACTER and have a length of n, when this record is built in a Pre 9.0 Unicode DB, the field length automatically gets converted to 3n(bytes). That means FIELD will be of type VARCHAR(3n) in the backend. This feature is used to support huge characters like Chinese, which may come in an unicode DB. One Chinese Character is almost equal to 3 bytes, hence if we build the record with the same length as in appdesigner (as we do for normal non-Unicode DB), it will be difficult to accommodate the Unicode characters. In non Unicode DB tripling is not needed as most of the characters are one byte.
In Pre 9.0 DBs the value of the NLS_LENGTH_SEMANTICS init parameter is set to BYTE (by default NLS_LENGTH_SEMANTICS value is BYTE). That means filed length in DB is interpreted as bytes. This is the reason why Peoplesoft triples the field length to accommodate enough bytes coming from Unicode character sets. NLS_LENGTH_SEMANTICS can also be set to CHAR in oracle, and this allows the length to be treated in Characters rather than bytes. Peoplesoft started adopting this feature from apps 9.0 onwards. That means from 9.0 onwards Unicode DBs should have init parameter NLS_LENGTH_SEMANTICS set to CHAR and there will not be any tripling of field lengths as length is treated in characters.
In a pre App9 DB a field of length 1 in appdesigner will show as VARCHAR(3) in backend and in 9.0 onwards it will show as VARCHAR(1 CHAR) in backend.
LOB and CLS come together with App9 and higher. For Unicode DBs both features will be there and for non-Unicode DBs only LOBS will be enabled
CLS is a feature introduced in Apps9 and above and is supported only for Unicode Oracle DBs. Before Apps9 to support complex character sets such as Chinese, Peoplesoft used a method called tripling feature. This means a field say FIELD1 in appdesigner which is of type CHARACTER and have a length of n, when this record is built in a Pre 9.0 Unicode DB, the field length automatically gets converted to 3n(bytes). That means FIELD will be of type VARCHAR(3n) in the backend. This feature is used to support huge characters like Chinese, which may come in an unicode DB. One Chinese Character is almost equal to 3 bytes, hence if we build the record with the same length as in appdesigner (as we do for normal non-Unicode DB), it will be difficult to accommodate the Unicode characters. In non Unicode DB tripling is not needed as most of the characters are one byte.
In Pre 9.0 DBs the value of the NLS_LENGTH_SEMANTICS init parameter is set to BYTE (by default NLS_LENGTH_SEMANTICS value is BYTE). That means filed length in DB is interpreted as bytes. This is the reason why Peoplesoft triples the field length to accommodate enough bytes coming from Unicode character sets. NLS_LENGTH_SEMANTICS can also be set to CHAR in oracle, and this allows the length to be treated in Characters rather than bytes. Peoplesoft started adopting this feature from apps 9.0 onwards. That means from 9.0 onwards Unicode DBs should have init parameter NLS_LENGTH_SEMANTICS set to CHAR and there will not be any tripling of field lengths as length is treated in characters.
In a pre App9 DB a field of length 1 in appdesigner will show as VARCHAR(3) in backend and in 9.0 onwards it will show as VARCHAR(1 CHAR) in backend.
LOB and CLS come together with App9 and higher. For Unicode DBs both features will be there and for non-Unicode DBs only LOBS will be enabled
From 8.50
From Peoplesoft 8.50 onwards (tools related) the DATETIME and TIME data types are changed to TIMESTAMP. This is a feature introduced from PT850 (Irrespective of Apps Version)
All Together-Very ImportantIn short CLS and LOBS are an App9 feature (not dependent on tools) and TIMESTAMP is a PT850 feature (not dependent on Apps_)
DATABASE_OPTIONS
DATABASE_OPTIONS is a critical column where the data type changes are recorded. This column is introduced from 847 onwards. Let’s see what all values this column can take. This column value changes during upgrade process, and is a critical column for the peoplesoft tools functionality.
· If the value is zero =neither Apps9 data types nor TIMESTAMP is enabled on the DB. This means Peoplesoft is using old data type.
· If the value is 2 then PeopleSoft is using the new ORA data types (CLS and LOBS).No timestamp is enabled
· If the value is 32, then PeopleSoft is using old data types, (Longs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled
· If the value is 34 then PeopleSoft is using the new Oracle data types supported with PT8.48 (Longs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled
From Peoplesoft 8.50 onwards (tools related) the DATETIME and TIME data types are changed to TIMESTAMP. This is a feature introduced from PT850 (Irrespective of Apps Version)
All Together-Very ImportantIn short CLS and LOBS are an App9 feature (not dependent on tools) and TIMESTAMP is a PT850 feature (not dependent on Apps_)
DATABASE_OPTIONS
DATABASE_OPTIONS is a critical column where the data type changes are recorded. This column is introduced from 847 onwards. Let’s see what all values this column can take. This column value changes during upgrade process, and is a critical column for the peoplesoft tools functionality.
· If the value is zero =neither Apps9 data types nor TIMESTAMP is enabled on the DB. This means Peoplesoft is using old data type.
· If the value is 2 then PeopleSoft is using the new ORA data types (CLS and LOBS).No timestamp is enabled
· If the value is 32, then PeopleSoft is using old data types, (Longs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled
· If the value is 34 then PeopleSoft is using the new Oracle data types supported with PT8.48 (Longs and Legacy Unicode implementation) with TIMESTAMP post PT8.50 conversion enabled
The build process using appdesigner or Data mover have a great impact on this value. This value is checked before any build process and the datatype that should be used during build is decided based on this value. For example if the value is zero VARCHAR fields are built as LONG and if the value is 32 they are built as CLOBs or BLOBs. So if there is any issue with this value the system may go to an inconsistent state.
Install
There is not going to be any bigger impact if we are doing a fresh install, as the datatypes and database_options columns are set automatically.
For PT847,PT848,PT849 System DBs DATABASE_OPTIONS will be zero- as no new datatypes are supported
For PT850, 851 system DBs (PTSYS) DATABASE_OPTIONS will be 32- as only TIMESTAMP is enabled
For Apps9 DB on PT848 and 849 DATABASE_OPTIONS will be 2-Only Apps 9 data type change is enabled
For Apps9 or 91 DBs on PT850 and PT851 DATABASE_OPTIONS will be 34-Both TIMESTAMP and APPs9 change are enabled
For below Apps9 DBs on PT847, PT848, PT849 DATABASE_OPTIONS will be zero- No new Datatypes are enabled
For below Apps9 DBs on PT850, 851 DATABASE_OPTIONS will be 32- Only timestamp is enabled
Relscripts
Relscripts as the name indicates are used to rel up the tools versions. Relscripts contains all the managed tools tables used by Peoplesoft (These are core tables and is needed for basic functionality). Note that relscript contains only core tables and will not contain all system tables. The remaining system tables will be built during the PPLTLS84CUR build process in upgrades.
Normally relscripts comes as the first step in tools upgrade process. Without these tables built normal Appdesigner features, DM and other tools basic functionalities won’t work. This is the reason that relscripts are executed as an initial step so that the essential tools changes are built before we copy PPLTLS84CUR with appdesigner which contains all the new tools objects. If we don’t execute rel scripts we won’t be able to operate even the new tools Appdesigner and DM, by which we complete remaining tools upgrade steps..
In oracle there are four different types of relscripts available from 848. Let’s see why we have four different relscripts
Rel84x.sql- This is the normal relscripts used for non-Unicode pre Apps9 DBs. Pre App9 DBs have the old datatypes and when we upgrade them to new tools we want to use the same data type in which the DB is built. Rel84x.sql contains the old datatypes.
Rel84xn.sql- This is used for all upgrades where target DB is on Apps9 or above. These DBs already uses the new datatypes hence this script is needed as rel84xn contains new datatypes. If we use rel84x.sql instead, this will create the tools tables with old datatypes and will cause issues later.
Rel84xu.sql and rel84xun.sql are the Unicode version of the above scripts
Upgrades
• In Upgrade process the data types are changed using
• Relscripts
• Converting Oracle Time Data types: PSORADataTypesConversion.bat- This is the step where all the Database tables with LONG and LONG ROWS are converted to CLOBS or BLOBS.
This step implements the CLS feature also if the DB is Unicode. This is a crucial step in Unicode DB upgrades. Right before this step you should make the NLS_LENGTH_SEMANTICS parameter to CHAR and execute this step. This step will decrease the column lengths which were tripled earlier. If the parameter is set incorrectly you will face issues. (Step is needed only if you are Upgrading a Pre App9 DB to App9 or above).
• ConvertingDatabasDatatypes: PSORATimestampConversion.bat- This step converts all DATETIME and TIME columns in the DB to TIMESTAMP. (step is need only if you are upgrading a pre-850 release to 850 or above)
The column DATABASE_OPTIONS is changed in the following Upgrade steps.• Updating the Database for Timestamp- This updates database_options to indicate that new timestamp data type is now enabled. The script name is UPGDBOPTIONS_ENABLETIMESTAMP.SQL. This step normally comes after executing the relscripts. The relscript for 850 comes with TIMESTAMP datatypes. So onceyouu rel up the tools to 850 this step is executed to indicate that any further builds will utilize the 850 feature and DB is now in 850.
• Updating Database Options- This updates database_options to indicate that new data types (LOBS and CLS) is now enabled. The script name is UPGDBOPTIONS_ENABLE.SQL. This step is executed right after the new datatypes are enabled. Means after executing all the steps generated by “ConvertingDatabasDatatypes: SORATimestampConversion.bat” step. After this step any further build process will build the tables with LOBs and CLS (for Unicode)
There is not going to be any bigger impact if we are doing a fresh install, as the datatypes and database_options columns are set automatically.
For PT847,PT848,PT849 System DBs DATABASE_OPTIONS will be zero- as no new datatypes are supported
For PT850, 851 system DBs (PTSYS) DATABASE_OPTIONS will be 32- as only TIMESTAMP is enabled
For Apps9 DB on PT848 and 849 DATABASE_OPTIONS will be 2-Only Apps 9 data type change is enabled
For Apps9 or 91 DBs on PT850 and PT851 DATABASE_OPTIONS will be 34-Both TIMESTAMP and APPs9 change are enabled
For below Apps9 DBs on PT847, PT848, PT849 DATABASE_OPTIONS will be zero- No new Datatypes are enabled
For below Apps9 DBs on PT850, 851 DATABASE_OPTIONS will be 32- Only timestamp is enabled
Relscripts
Relscripts as the name indicates are used to rel up the tools versions. Relscripts contains all the managed tools tables used by Peoplesoft (These are core tables and is needed for basic functionality). Note that relscript contains only core tables and will not contain all system tables. The remaining system tables will be built during the PPLTLS84CUR build process in upgrades.
Normally relscripts comes as the first step in tools upgrade process. Without these tables built normal Appdesigner features, DM and other tools basic functionalities won’t work. This is the reason that relscripts are executed as an initial step so that the essential tools changes are built before we copy PPLTLS84CUR with appdesigner which contains all the new tools objects. If we don’t execute rel scripts we won’t be able to operate even the new tools Appdesigner and DM, by which we complete remaining tools upgrade steps..
In oracle there are four different types of relscripts available from 848. Let’s see why we have four different relscripts
Rel84x.sql- This is the normal relscripts used for non-Unicode pre Apps9 DBs. Pre App9 DBs have the old datatypes and when we upgrade them to new tools we want to use the same data type in which the DB is built. Rel84x.sql contains the old datatypes.
Rel84xn.sql- This is used for all upgrades where target DB is on Apps9 or above. These DBs already uses the new datatypes hence this script is needed as rel84xn contains new datatypes. If we use rel84x.sql instead, this will create the tools tables with old datatypes and will cause issues later.
Rel84xu.sql and rel84xun.sql are the Unicode version of the above scripts
Upgrades
• In Upgrade process the data types are changed using
• Relscripts
• Converting Oracle Time Data types: PSORADataTypesConversion.bat- This is the step where all the Database tables with LONG and LONG ROWS are converted to CLOBS or BLOBS.
This step implements the CLS feature also if the DB is Unicode. This is a crucial step in Unicode DB upgrades. Right before this step you should make the NLS_LENGTH_SEMANTICS parameter to CHAR and execute this step. This step will decrease the column lengths which were tripled earlier. If the parameter is set incorrectly you will face issues. (Step is needed only if you are Upgrading a Pre App9 DB to App9 or above).
• ConvertingDatabasDatatypes: PSORATimestampConversion.bat- This step converts all DATETIME and TIME columns in the DB to TIMESTAMP. (step is need only if you are upgrading a pre-850 release to 850 or above)
The column DATABASE_OPTIONS is changed in the following Upgrade steps.• Updating the Database for Timestamp- This updates database_options to indicate that new timestamp data type is now enabled. The script name is UPGDBOPTIONS_ENABLETIMESTAMP.SQL. This step normally comes after executing the relscripts. The relscript for 850 comes with TIMESTAMP datatypes. So onceyouu rel up the tools to 850 this step is executed to indicate that any further builds will utilize the 850 feature and DB is now in 850.
• Updating Database Options- This updates database_options to indicate that new data types (LOBS and CLS) is now enabled. The script name is UPGDBOPTIONS_ENABLE.SQL. This step is executed right after the new datatypes are enabled. Means after executing all the steps generated by “ConvertingDatabasDatatypes: SORATimestampConversion.bat” step. After this step any further build process will build the tables with LOBs and CLS (for Unicode)
Great post! This information is very important for which there are not many posts.
ReplyDeleteIn upgrades, if the LONGtoCLOB sqls are generated with no conversion statements, smell the danger and revisit the value set for NLS_LENGTH_SEMANTICS.
Thanks! Keep blogging.