This chapter discusses how to:
Define remote database connections.
Define installation options.
Define subject areas.
Note. These activities are typically one-time events and require minimal maintenance.
This section provides an overview of remote database connections and discusses how to define remote database connections.
Data sources represent the location of the source data that is extracted, transformed, and loaded to the target. Remote data source data is extracted from a separate (remote) database and migrated into the local database. You must define remote database connections to source data from a database other than your local PeopleSoft database instance.
The Remote Database Access Management page enables you to define connectivity information for relational databases to be used for sourcing data for PeopleSoft Data Transformer. You can define connectivity information only for databases of the same type as the Target PeopleSoft database instance. For example, if the PeopleSoft database instance is Oracle, then you can define only Oracle remote connections. Remote database connections are established once for a local connect and once for each database that will be sourced. You must define one local connection for the current PeopleSoft instance. This connection is used during remote database access to retrieve information for the target database.
Note. If you are accessing data from a local database, then you do not need to set up remote database connections.
Here is a recommended procedure for setting up data transformation in an environment (be sure to replace the sample information used in this procedure with your environment and database information):
Install JDBC driver for the test database.
For SQL Server, download the appropriate JDBC driver version from the Microsoft Download Center. After the installation, three jar files are created under the JDBC driver library directory, for example, C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib. To make these files accessible to the application server, either copy them to <ps_home>\class, or add them to the CLASSPATH line in the psappsrv.cfg file (application server configuration file), for example:
CLASSPATH=C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar
Set up a local database connection (for example, H881C338).
In PIA, navigate to Enterprise Components, Data Transformation, Setup Remote Database Connection, Remote Database Access Management. Add a local database connection with sample information as follows:
Name: H881C338
Local Connect: Selected
Description: H881C338
Server: ECHAN2091903
User ID: sa
Password: Peoples0
Save the page and click the Test Connection button to verify that the connection information entered is correct. The Good Connection message appears if the information is correct.
Set up a remote database connection (for example, HC881TM1)
Navigate to Enterprise Components, Data Transformation, Setup Remote Database Connection, Remote Database Access Management. Add a remote database connection with sample information as follows:
Name: HC881TM1
Description: HC881TM1
Server: ADDB0106
User ID: satst
Password: satst
Save the page and click the Test Connection button to verify that the connection information entered is correct.
Set up data transformation installation options.
Navigate to Enterprise Components, Data Transformation, Data Transformation Installation Options. On the Data Transformation Installation Options page, enter the following sample information:
Maximum Rows to Preview: 10
Tablespace Name: EOECLRG
Message Set Number: 18024
Save the page.
Define subject area.
Navigate to Enterprise Components, Data Transformation, Setup Subject Areas. Add a subject area by entering the following sample information:
Subject Area: NACNV
Description/More Information: Enter some text
Default Subject Area: Selected
Create Temporary Table: Selected
Save the change.
Define source data object.
Navigate to Enterprise Components, Data Transformation, Define Source Data Objects, Source Data Object. Add a new source data object by entering the following sample information:
Source Data Object: DEPARTMENT
Subject Area: NACNV
Description: Department
Data Source Type: Remote
Data Source Name: HC881TM1
Source Record (left field): PS_DEPT_TBL
Source Record (right field): Department Table
Navigate to the Fields page and click the Synchronize fields button. Select these fields:
SETID, DEPTID, EFFDT, EFF_STATUS, DESCR, DESCRSHORT, and COMPANY
Save the page.
Set default values for target fields.
Navigate to Enterprise Components, Data Transformation, Set Target Field Defaults. Add two target fields using the following sample information:
Target Field: SETID
Source Input Value: SNSZZ
Target Field: DEPTID
Source Input Value: ABCDE
Save the change.
Define map information.
Navigate to Enterprise Components, Data Transformation, Define Maps. Add a map object using the following sample information:
Map Object: DEPT
Subject Area: NACNV
Description: Department ETL
Source: DEPARTMENT
Target: DEPT_TBL
Navigate to the Map Field Detail page and click the Apply AutoMapper button. Next, click the Source Input Type column heading twice to bring the rows with non-blank source input type to the top of the list. Change the source input type for the SETID and DEPTID target fields to Default. Change the source input type for the EFFDT target field to Source and select EFFDT as the source prompt because the Auto Field Mapper sets this to blank by default. Now there should be seven fields with non-blank source input types. Navigate to the Preview Map Results page and click the Preview button. Note that 10 rows of DEPT_TBL in HC881TM are displayed with SETID and DEPTID showing the default values set up in step 7.
Test the data transformation setup.
When testing the Data Transformer ETL, note that:
— The 3 jar files in step 1 will need to be added to psprcs.cfg (process scheduler configuration file) if they were not copied into <ps_home>\class
— A successful run depends on what data exists in the source table and the target table, otherwise the job may end with a duplicate error.
To verify that the ETL can correctly access the remote table data, delete all the rows in my local PS_DEPT_TBL, set the seven fields in the map to use Source as source input type and run the ETL. To do so, navigate to Enterprise Components, Data Transformation, Run Data Transformations, Run Data Transformations. Add a run control using the following sample information:
Data Transformer Object Type: Map
Map Object: DEPT
Target Load Option: Full Load
Destructive Load: Clear
Parallel Processing: Selected
Click the Run button. Two processes are executed: EOEW_ETL_EXE and EOEW_ETL_RUN.
Note that as the two processes run to Success, in Query Analyzer, the PS_DEPT_TBL rowcount matches that in the source table.
Note. If the data transformation process is not loading data into target tables, the problem may originate from the CLASSPATH, which was defined in your system. Also, this behavior can be caused by the process scheduler if it doesn't have the right access to the JDBC drivers.
Oracle Database Considerations
If you are using an Oracle database, you need to update the content of both psappsrv.cfg and psprcs.cfg to reflect your Oracle connectivity settings. Basically you should determine the Oracle home directory and specify it in both psappsrv.cfg and psprcs.cfg by adding the following two lines under the ;JavaVM Shared Library= section:
; RDBA Oracle JDBC driver
Add to CLASSPATH=
Add to CLASSPATH=
PREPARE TO CONFIGURE ORACLE -
Before you can use remote data access with Oracle, the appropriate database connectivity software must be installed on the system where the application server or batch server is running. The minimum database connectivity that needs to be installed is the Oracle Client Net9i 9.2.0.2.0 Connectivity Package.
To connect to a remote Oracle database, you must manually edit the application server configuration file, psappsrv.cfg, as described below. If the batch server is being used, you must manually edit the batch server configuration file, psprps.cfg, as well.
From the Remote Database Connection page (PeopleTools, Utilities, Administration, Remote Database Connection), you can specify an Oracle datasource as specific or with TNSNAMES. Specific doesn't require a TNSNAMES entry, and will use the Oracle "thin" JDBC driver. If TNSNAMES is configured for the remote database, you can use the TNSNAMES style entry.
CONFIGURING ORACLE 9i OR ORACLE 10g ON UNIX -
Determine the Oracle home directory and specify it in the configuration files by adding the following two lines under the ;JavaVM Shared Library= section:
; RDBA Oracle JDBC driver
Add to CLASSPATH=%ORACLE_HOME%\jdbc\lib\ojdbc14.jar
Add to CLASSPATH=%ORACLE_HOME%\jdbc\lib\orai18n.jar
CONFIGURING ORACLE 9i OR ORACLE 10g on WINDOWS -
Determine the Oracle home directory and specify it in the configuration file. For example, if Oracle the home directory is C:\Apps\DB\Oracle901, add the following lines under the ;JavaVM Shared Library= section:
; RDBA Oracle JDBC driver
Add to CLASSPATH=C:\Apps\DB\Oracle901\jdbc\lib\ojdbc14.jar
Add to CLASSPATH=C:\Apps\DB\Oracle901\jdbc\lib\orai18n.jar
Page Name |
Definition Name |
Navigation |
Usage |
Remote Database Access Management |
REMOTEDB |
Enterprise Components, Data Transformation, Setup Remote Database Connection, Remote Database Access Management |
Define remote database connections. |
Access the Remote Database Access Management page (Enterprise Components, Data Transformation, Setup Remote Database Connection, Remote Database Access Management).
Important! Different fields may appear on the page based on the database DB type. The preceding page shows fields for Microsoft, DB2 (OS/390), DB2/UNIX, and Sybase.
This table illustrates the page variance by database type:
Database Type |
Required Information |
Microsoft, DB2 (OS/390), DB2/UNIX, and Sybase |
Enter a description, server, database, userID, and password. |
Informix |
Enter a description, server, user ID, password, and Inf Svr (Informix Server) name. |
Oracle |
For Oracle and a Connection Type of TNSNames, enter a description, TNS Entry, user ID and password.
Note. TNS Names is a preconfigured file (tnsnames.ora) with
previously defined database connection information. |
Name |
Displays the remote DB name entered on the Search page for your database. |
DB Type |
Displays the database type of your PeopleSoft database. Note. Since your remote database must be of the same type, this field is defaulted to your current local database type. |
Local Connect |
Select if you are defining the remote connection information for your local database. All others must be specified as remote databases. An error displays if you save a page with a connection checked as a Local Connect and there is already one identified as such (in the PSREMOTEDBDEFN table). Important! One connection must be defined as the Local Connect, the connection information for the current PeopleSoft instance (the local database). This connection is used during remote database access to retrieve connection information for the source and as the target database for the sourced data. |
Server |
Enter the name of the server where the database resides. Note. This field is platform specific. |
DB Server Port |
The DB Server Port number is automatically populated with a default value based on the database type. It may need to be changed depending upon your database server configuration. |
User ID and Password |
Enter the user ID and password that are required to access the database. The password is not displayed. Note. This field is platform specific. |
Test Connect |
Click to access the remote database by using the information that you have entered. This will verify that all of the connection information is correct. |
See Also
PeopleSoft Hardware and Software Guide
This section discusses how to define installation options.
Page Name |
Definition Name |
Navigation |
Usage |
EOEW_OPTIONS |
Enterprise Components, Data Transformation, Data Transformation Installation Options |
Define installation options. |
Access the Data Transformation Installation Options page (Enterprise Components, Data Transformation, Data Transformation Installation Options).
System administrators may use PeopleSoft Data Transformers' installation options to set certain high-level Data Transformer defaults such as the maximum number of rows to return on a preview of sourced or transformed data, a table space name to be used to dynamically build temporary tables, and the default message set number assignment for transformation error correction.
Enter the maximum number of rows of sourced or transformed data to preview. Keep this value relatively low, for example 100 rows, as larger values significantly increase the amount of time it takes to retrieve data and render the preview pages. |
|
Enter the tablespace name for the temporary table that is built dynamically at runtime. This tablespace name is applied to all of the temporary tables unless overridden at a lower level. Note. This name can be overridden at the subject area and map option levels. |
|
Select to create temporary tables dynamically. A temporary table is created at runtime—it's needed by the engine during processing only, then it's deleted at completion. It is not available in PeopleSoft metadata. If the check box is cleared, you must specify a temporary table record name on the map for use at runtime. This temporary table will be available in PeopleSoft metadata and remain after the engine processing. Note. Determining which of these options to use needs to be considered carefully. A dynamic temporary table will automatically change when the map changes, conversely, a non-dynamic temporary table will not change and may require more maintenance. |
|
Select a message set number that corresponds to a text error message set. When adding a new transformation and associating a specific error message with an error, this default message set number is used as a starting point. |
See Also
Updating Map Processing Options
This section provides an overview of subject areas and discusses how to define subject areas.
Use subject areas as a grouping mechanism on translation sets, transformations, map rules and map definitions. These objects can only be shared within a subject area. An enterprise subject area can be used for objects that are to be shared by all subject areas.
You need to carefully plan subject areas when you implement PeopleSoft Data Transformer. For example, you need to consider how the Data Transformer objects (translation sets, transformations, map rules, map definitions, and so on) can be grouped most efficiently. Some objects need to be available to all users, while some only to a specific user or functional group.
Note. Subject areas affect only prompting and object selection and do not reflect object security.
You can set up default tablespaces, or you can dynamically create temporary tables by subject area. If an implementation requires that only groups of maps dynamically generate temporary tables, or are built within different tablespaces, you can specify this at the subject area level.
See Also
Page Name |
Definition Name |
Navigation |
Usage |
EOEW_OBJ_OWNER |
Enterprise Components, Data Transformation, Setup Subject Areas |
Define subject areas to group translation sets, transformations, map rules, and map definitions. |
Access the Subject Areas page (Enterprise Components, Data Transformation, Setup Subject Areas).
Select to use this subject area as your default subject area. Note. Only one subject area can be selected as the default, which becomes available to all users—it is your enterprise subject area. |
|
Tablespace Name |
Select the tablespace name to use if you want to override the Data Transformation installation options selection for this subject area.
Note. This is needed only when an implementation shares
dynamically generated temporary tables over multiple tablespaces. |
Create Temporary Table |
Select if you want to override the Data Transformation installation options selection for this subject area. This is needed only when an implementation allows or disallows the dynamic temporary table creation per subject area. |