To create uniface models (data dictionaries) of sybase databases

Convert Tables from Sybase to Oracle

This document will detail a process in which to convert a Sybase database to an Oracle database. It begins with the creation of the Oracle DDL via Uniface. Next it applies custom changes to the DDL via a perl script. After that, the DDL is executed and then the data is loaded via another perl script. Within this process, there are 3 sets of reconciliation, one to reconcile the uniface definitions, one to reconcile the Oracle definitions with the Sybase definitions, and one to reconcile the Oracle data with the Sybase data.

I. Create Uniface Models of Sybase Databases *

Backup existing uniface models *

Create new model in Uniface (DSSLOAD) *

Load definitions into Uniface by reading Sybase catalogs *

Reconcile the Uniface definitions *

Backup uniface models *

Create and load the uniface_defs table (old definitions) *

II. Perform Global Changes to Uniface Models *

Get list of DSSLOAD tables with no indexes – add dummy index to Uniface *

Create New Uniface Model to Hold Converted Model Definitions *

Unload the model to a CIF text file *

Edit the CIF text file *

Load the model from the edited CIF text file *

Make any manual changes required *

Create and load the uniface_defs2 table (new definitions) *

Analyze the model *

III. Create New Oracle Tables *

Create the Oracle DDL *

Parse the DDL and add custom specifications for the installation *

Create the Oracle tables by executing the DDL files *

Create the Oracle view DDL by modifying Sybase view DDL *

Reconcile the new Oracle definitions with the old Sybase definitions *

IV. Transfer data from Sybase to Oracle *

load_schema.pl script. *

Reconcile the new Oracle data with the old Sybase data *

V. To transfer data from Sybase to Oracle tables using FileAid *

Create FileAid Converter script for each table *

Transfer the FileAid Converter script to server *

Execute the Conversion *

Create Uniface Models of Sybase Databases

Backup existing uniface models

Backup the existing uniface repository. (The repository was local on the computer doing the initial conversion, so to backup, the c:\usys72\project directory was simply copied.)

Create new model in Uniface (DSSLOAD)

Uniface – Construction – Model – menubar Edit – New Model

Load definitions into Uniface by reading Sybase catalogs

Uniface – menubar Go To – Administration – Exchange Models – Load Definitions…

Choose Sybase

Choose the model you just created (DSSLOAD)

In ‘Database Name’ field, type in server.databasename (ex. syb_dss_conv.cm_dss_d)

(When doing 2^nd database, must exit uniface entirely before starting because always remembers last database used and won’t prompt you for database name 2^nd time.)

Type in username and password to log into sybase database

Add all of the tables by hitting the Add button as many times as necessary

(Once the focus is on the Add button ,can just hold down enter key and it will repeat add.)

Load the table definitions into the Uniface model by hitting the Load button

Note any problems encountered by going to Uniface – menubar View – Message Frame

When duplicate tables found, must rename a table, so create new model (DSSLOAD2) and load the duplicates that failed into it. Then in Uniface go to Model – choose model (DSSLOAD2) – hit the Entities button. Select the entity and then go to menubar Edit – Duplicate Entity. Click once on model (DSSLOAD2) and now select the model you want table to go to (DSSLOAD) by double clicking on it. Up top, select the entity name automatically put in there and put in the name desired. Hit OK. Now remove the temporary model (DSSLOAD2): Uniface – Construction – Model – menubar Edit – Delete Model

Reconcile the Uniface definitions

The goal is to reconcile the new fields in the DSS model with their same named counterparts in the existing FIS models (IU, CAM…) and produce a report listing only the differences. This is done by dumping the uniface model to an oracle table and then doing sql queries to produce the report.

Create uniface form so can unload all model definitions

Set up uniface printing

Run form

Print to file

Manipulate file – take out page breaks

Create table to hold model information (model_all)

Load file into table - sqlldr

Remove unneeded model information (DICT, UVCS…)

Copy to another table splitting up shorthand field (model_all2)

Perform Global changes that will occur later and also eliminate non-problem differences

Change datatype "SS" to "S" Load Definitions incorrectly brought in SS
Change datatype "C" to "VC" All Char fields will be changed to Varchar2
Change shorthand "M4" to "M2" Load Definitions incorrectly brought in M4

Create table and group up the FIS definitions, adding a count
From that, create list of only the most commonly used FIS definitions
Remove any duplicates
Create and populate the table to hold the report
Remove from report if difference is only uniface dates
Print Reports DSS differences, DSS differences detail, FIS inconsistencies

Create uniface form so can unload all model definitions

Create a form that will read models and display in format we want ("CHK_MODEL"):

To create forms that access uniface data dictionary:

Utilities, Import, C:\usys72\trx\umeta.trx (this creates the DICT model you can read from)

New form, paint entity and rename to DICT.UCFIELD or some other U* table you want.

Note – change the DICT.UCFIELD entity to "No Updates" to avoid inadvertently updating the uniface model definitions.

Set up uniface printing

These instructions detail how to set up the printing function in a uniface development environment.

Start, Settings, Printers, Add Printer

Managed by My Computer… Next

From Available Ports choose FILE… Next

From List of Manufacturers, choose Generic / Text Only

In Uniface, File, Print, Hit ">>" next to Print Job Model

In Browse Print Job Model window, hit New button

In Define Print Job Model, Type in name ("FILE" in our case)

In Device Type Hit ">>" next to it

Choose "P_MSWINX" from list

In Print Queue type "PRINTMAN"

In Device Mode choose "0"

Choose "black on white"

Run Form

To run the form that was just created, open from and, from the File menubar, choose "Test". This will run the form and show you a screen with no data on it. To retrieve all of the model definitions, hit the right mouse button and choose "Retrieve Data" or hit the key sequence:Gold, R (where Gold = keypad "+" and then the " * " key).

Note: To only retrieve a subset of data (only one model’s definitions), enter the model into the model field and then retrieve the data.

Retrieve the definitions for all of the models.

Print to File

File, Print – Under Print Job Model, choose the "FILE" model – hit ok, choose Generic/Text Only and click the Print To File box. Specify a file name "uniface_models.txt"

Manipulate the new file – take out page breaks

Using MS Word, remove the first few header lines manually

Take out page breaks in file:

Use MS Word –Edit – Replace – Hit the More button

Put cursor in Find What and hit the Special button – Choose Manual Page Break

Put cursor in Replace With and hit the Special button – Choose Manual Line Break

Save the file with a new name – uniface_models_clean.txt – make sure it’s a text file

(there’s still an extra character in there but sqlldr just ignores it when loading)

Create table to hold model information (model_all)

Cut and paste the following SQL into Oracle SQLPLUS to create the table to hold all the uniface model information.

CREATE TABLE "MODEL_ALL"(

"MODEL" VARCHAR2(32),

"ENTITY" VARCHAR2(32),

"FIELD" VARCHAR2(32),

"DATATYPE" VARCHAR2(2),

"SHORTHAND" VARCHAR2(64),

CONSTRAINT "MODEL_ALL_TP1" PRIMARY KEY(

"MODEL",

"ENTITY",

"FIELD" ))

Load the file into the new table using SQL Loader (sqlldr)

SQL Loader allows you to read a file and load it into an Oracle table. The control file describes the layout of the file to be read. To create the control file, put the following in a text file called model_all.txt:

load data

infile 'uniface_models_clean.txt'

append

into table MODEL_ALL

(

MODEL POSITION(001:014) CHAR,

ENTITY POSITION(015:034) CHAR,

FIELD POSITION(035:062) CHAR,

DATATYPE POSITION(063:071) CHAR,

SHORTHAND POSITION(072:078) CHAR

)

Then, from the DOS prompt, type the following (where userid and password are changed to their respective values):

sqlldr80 userid=userid/password@dba2dev control=model_all.ctl

After running, it creates a log file in the local directory called model_all.log which will give the details of how many rows were loaded and any errors. Look for any errors in the log file. It is normal (in this conversion process) to have the error "Record 9999: Discarded - all columns null.".

Remove unneeded model information (DICT, UVCS…)

Access the oracle database and remove the unneeded model information that was loaded into the model_all table. To get a list of all the models in the table, type:

select distinct model from model_all;

From them, choose the models that are not pertinent to this process and type the following to remove them from the table:

delete from model_all where model = 'ARRAYS';

delete from model_all where model = 'DICT';

delete from model_all where model = 'PRINTER';

delete from model_all where model = 'SYSENV';

delete from model_all where model = 'UVCS';

This will remove the non-database entity SF-ARRAY. (Removing this now will clear up the reconciliation reports later since it has some strange definitions on it which end up being of no concern to the process since it is a non-database entity.)

delete from model_all where entity = 'SF_ARRAY';

The "good" models are:

CAM

DSSLOAD

PR_SUB_ACCT

SPUFE

Copy to another table (model_all2) splitting up the shorthand field

Copy the existing data to another table, splitting up the shorthand field into 2 fields, one to hold the alphanumeric portion, and one to hold the numeric portion. This is done so that the shorthand (also called the "packing code" in uniface) field can be manipulated easily.

CREATE TABLE "MODEL_ALL2"(

"MODEL" VARCHAR2(32),

"ENTITY" VARCHAR2(32),

"FIELD" VARCHAR2(32),

"DATATYPE" VARCHAR2(2),

"SHORTHAND1" VARCHAR2(10),

"SHORTHAND2" VARCHAR2(10),

CONSTRAINT "MODEL_ALL2_TP1" PRIMARY KEY(

"MODEL",

"ENTITY",

"FIELD" ))

insert into model_all2

select model, entity, field, datatype,

TRANSLATE(shorthand,

'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',

'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),

TRANSLATE(shorthand,

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

'0123456789')

from model_all

Perform global changes

Make the future global changes to all of the models (the new DSSLOAD and the existing models) so that we can reconcile the definitions the way they will be. (There would be too many differences to compare them without doing the global changes.) Note: These are the intended future global changes – just being done here for the reconciliation steps – this is not the step that makes the actual changes to the uniface data models. The changes and their reasons are:

Change datatype "SS" to "S" Load Definitions incorrectly brought in SS

Change shorthand "C" to "VC" All Char fields will be changed to Varchar2

Change shorthand "M4" to "M2" Load Definitions brought in as M4

The sql to execute to make these changes is:

select count(*) from model_all2 where datatype = 'SS';

select count(*) from model_all2 where shorthand1 = 'C'

update model_all2 set datatype = 'S' where datatype = 'SS';

update model_all2 set shorthand1 = 'VC' where shorthand1 = 'C';

To preserve any M4 definitions that were already there, do a query to find how many there are and then find the exact ones:

select model, count(*) from model_all2 where datatype = 'N' and shorthand1 = 'M' and shorthand2 = '4'

group by model

MODEL COUNT(*)

-------------------------------- ----------

DSSLOAD 699

IU 1

select * from model_all where shorthand = 'M4' and model != 'DSSLOAD';

MODEL ENTITY FIELD DA Short

-------------------------------- -------------------------------- -------------------------------- -- -----

IU FP_INT_BILL_ITM_T FDOC_ITM_UNIT_AMT N M4

Make the global updates:

Update model_all2

Set shorthand2 = '2'

where datatype = 'N' and shorthand1 = 'M' and shorthand2 = '4'

700 rows updated.

Now fix the valid M4s:

select * from model_all2 where field = 'FDOC_ITM_UNIT_AMT';

MODEL ENTITY FIELD DA SHORTHAND1 SHORTHAND2

-------------------------------- -------------------------------- -------------------------------- -- ---------- ----------

IU FP_INT_BILL_ITM_T FDOC_ITM_UNIT_AMT N M 2

update model_all2 set shorthand2 = '4'

where field = 'FDOC_ITM_UNIT_AMT';

Create table and group up the FIS definitions, adding a count

Create a list of all the unique definitions for each field name that resides in the FIS models. Add a count of the number of times each unique definition is present. Use the following query:

create table fis_fields as

select field, datatype, shorthand1, shorthand2, count(*) "COUNT"

from model_all2

where model != 'DSSLOAD'

group by field, datatype, shorthand1, shorthand2

From that, create list of only the most commonly used FIS definitions

Create a list of only the most commonly used FIS definitions.

create table fis_most_common as

select field, datatype, shorthand1, shorthand2

from fis_fields a

where count =

(select max(count)

from fis_fields

where field = a.field)

Remove any duplicates

Remove any duplicates due to the fact that there may be 2 different data definitions used the same exact number of times - and that number could be the max. Deletes one of the data definitions using the min() function – just used so will only get one data definition to delete.

delete from fis_most_common a

where 1 < (select count(*) from fis_most_common where field = a.field)

and (datatype || shorthand1 || shorthand2) =

(select min(datatype || shorthand1 || shorthand2)

from fis_most_common

where field = a.field)

Create and populate the table to hold the report

Execute the query to create the report and put the results into a table called differences. This will display all the DSSLOAD fields that do not match the most commonly found definition in the other models. It will print the DSSLOAD field definition and the FIS most common definition.

create table differences as

select a.field, a.entity, a.datatype, a.shorthand1, a.shorthand2,

f.datatype "F_DATATYPE", f.shorthand1 "F_SHORTHAND1" , f.shorthand2 "F_SHORTHAND2"

from model_all2 a, fis_most_common f

where a.model = 'DSSLOAD'

and a.field = f.field -- joins to fis_most_common and prevents listing of fields that are in DSS only

and a.field || a.entity || a.datatype || a.shorthand1 || a.shorthand2

not in

(select field || entity || datatype || shorthand1 || shorthand2

from fis_most_common

where field = a.field

and datatype = a.datatype

and shorthand1 = a.shorthand1

and shorthand2 = a.shorthand2 )

Remove from report if difference is only uniface dates

Remove the differences that were discovered from the previous query, if the difference is simply uniface dates (they will both generate the Oracle "DATE" datatype)

delete from differences

where datatype in ('D', 'E')

and f_datatype in ('D', 'E')

Print Reports DSS differences, DSS differences detail, FIS inconsistencies

Create the Reconciliation report by selecting from the differences table. Either Cut and Paste the results from sqlplus (to MS Word) or use the spool command to direct the output to a flat file.

Print DSS Differences report

break on field skip 1

select * from differences

order by field

Print DSS Differences detail report

select * from model_all2 where field in (select field from differences) order by field;

Print FIS inconsistencies report

break on field skip 1

SELECT * FROM FIS_FIELDS A

WHERE 1 < (select count(*) from fis_fields where field = a.field)

order by field

Backup uniface models

Backup the uniface repository. (The repository was local on the computer doing the initial conversion, so to backup, the c:\usys72\project directory was simply copied.)

Create and load the uniface_defs table (old definitions)

Note: The model_all and model_all2 tables are only used for the previous reconciliation reports. The next steps use a similar table called uniface_defs and uniface_defs2 to do queries against the uniface data model. Uniface_defs will hold the models with the un-altered uniface definitions. Uniface_defs2 will hold all the new models that were copied from the originals and then globally and manually changed so that it would create the desired Oracle environment.

Follow the same instructions as in the previous steps for model_all. Create another form (CHK_MODEL2) that will load all the fields in this table. The new field is the IN_DB field which answers the question "Does this field exist In the Database?".

CREATE TABLE "UNIFACE_DEFS"(

"MODEL" VARCHAR2(32),

"ENTITY" VARCHAR2(32),

"FIELD" VARCHAR2(32),

"DATATYPE" VARCHAR2(2),

"SHORTHAND" VARCHAR2(10),

"IN_DB" VARCHAR2(1),

CONSTRAINT "UNIFACE_DEFS_TP1" PRIMARY KEY(

"MODEL",

"ENTITY",

"FIELD" ))

Perform Global Changes to Uniface Models

The next step in the process is to create new empty uniface models, export the uniface models to Case Interchange Format (CIF) files, make global changes to the CIF files, and import the changed files into the newly created empty uniface models.

Get list of DSSLOAD tables with no indexes – add dummy index to Uniface

This step fixes a problem that would be encountered later in the process. Since Uniface doesn’t generate DDL for tables that don’t have any indexes, get a list of DSS tables with no indexes from the sybase catalogs. For those tables, create a dummy primary key. This lets us generate the DDL, but then we must remove the create index ddl from the scripts later. The following sql should be done against all sybase DSS databases:

select t.name from

sysobjects t

where t.type = 'U'

and t.id not in

(select id from sysindexes

where id = t.id

and keys1 != null)

(Tables with no indexes: ca_hist_org_hier_gt, gl_hist_id_bill_gt, hu_78_85_acdapt_gt, hu_78_85_stfapt_gt, hu_hst_emp_nm_gt)

Add indexes to the uniface definitions by

Uniface – Construction – Model – Choose DSSLOAD - Hit Entities… button

For each table,

Hit the Keys… button. (Will show that there aren’t any fields defined to Key number 1. Hit the Add button once to add the first column to this new dummy key we are adding.

Create New Uniface Model to Hold Converted Model Definitions

Need to create an empty model to hold the converted existing models. Don’t want to overwrite any existing models in this process.

Uniface – Construction – Model – menubar Edit – New Model - Type in the name of the new model. The new models created in this process are:

Existing Model New Model

AR ODS_AR

CAM ODS_CAM

DSSLOAD DSS

DV ODS_DV

IU ODS_IU

PR_SUB_ACCT ODS_PR_SUB_ACCT

SPUFE ODS_SPUFE

Do the next 3 steps for each model (AR, CAM, DSSLOAD, DV, IU, PR_SUB_ACCT, SPUFE)

Unload the model to a CIF text file

Uniface – menubar Go To – Administration – Exchange Models – Case Unload

Type in the model

Leave the entity field blank to get all of the entities in that model

Use Version CIF 2.0

Type in a file name to create locally on your PC (*.CIF)

Hit the Unload button

Edit the CIF text file

Bring the newly created file into an editor such as MS Word

Note: All text typed should be in uppercase. It is advisable to use MS Word and set up macros to perform these global changes.

Manual Change:

Change the model name to the new model name. (It is on one of the first lines in the file and the line starts with "SX".) This is a simple change that doesn’t need to be in the MS Word macros.

Global changes:

Type "#I C" in the Find what: field

Type "#I VC" in the Replace with: field Replace All

Type "#Z SYB" in the Find what: field

Type "#Z DEF" in the Replace with: field Replace All

Type "#I M6" in the Find what: field

Type "#I M4" in the Replace with: field Replace All

DSS model only

Type "#T SS" in the Find what: field

Type "#T S" in the Replace with: field Replace All

ODS models only

The FIS will be changing all the SC1 -> SC2000 fields to VC1 -> VC2000. (But leaving all the SC* fields as is. Note the temporary change from "*" to the dummy code "~!@" and then the change right back to "*".)

Type "#I SC*" in the Find what: field

Type "#I ~!@" in the Replace with: field Replace All

Type "#I SC" in the Find what: field

Type "#I VC" in the Replace with: field Replace All

Type "#I ~!@" in the Find what: field

Type "#I SC*" in the Replace with: field Replace All

Type "#I M1" in the Find what: field

Type "#I M4" in the Replace with: field Replace All

Type "#I M2" in the Find what: field

Type "#I M4" in the Replace with: field Replace All

The following changes are for Uniface relationships. If they are not changed, there is an error on the next step of loading these models back in to uniface. (The error is a "relationship already exists" error, so we are changing the models specified in these relationships to the correct model names.)

Type ".AR->" in the Find what: field

Type ".ODS_AR->" in the Replace with: field Replace All

Type ".CAM->" in the Find what: field

Type ".ODS_CAM->" in the Replace with: field Replace All

Type ".DV->" in the Find what: field

Type ".ODS_DV->" in the Replace with: field Replace All

Type ".IU->" in the Find what: field

Type ".ODS_IU->" in the Replace with: field Replace All

Type ".PR_SUB_ACCT->" in the Find what: field

Type ".ODS_PR_SUB_ACCT->" in the Replace with: field Replace All

Type ".SPUFE->" in the Find what: field

Type ".ODS_SPUFE->" in the Replace with:field Replace All

The following change should be done manually at this point. It is searching for any left over M2s. Some are not found due to the line wrap of #I M2. #I may be on one line and M2 may be on the other line. This should be done manually in case there are any entities or fields that happen to have M2 in the name – each of these changes should be verified.

Type "M2" in the Find what: field

Type "M4" in the Replace with: field Replace All

Save the file as a new file (model DSS or model ODS_oldmodelname).

Load the model from the edited CIF text file

After the global changes have been made, we can now load the file back into the new Uniface Models.

Uniface – menubar Go To – Administration – Exchange Models – Case Load

Type in one of the models just created (DSS or ODS_xxxx)

Type in the name of the file that was just edited and saved

Hit the Load button

Note any problems encountered by going to Uniface – menubar View – Message Frame

Some problems were encountered where the existing model definitions, without any modifications done to them, would unload from uniface, but are rejected on load. (This is due to previous versions of Uniface allowing certain situations but the current version of uniface having stricter edits, is now preventing it – they can exist in the current version of uniface as long as they are not touched, which kicks off the uniface edits.) If any problems must be corrected in Uniface, go into uniface and make the changes and start again with the first Unload the model step.

I made changes to the original IU model because getting errors on load:

AR_INV_PMT_DESC.AR_PNDINVPMT_DOC_T

Changed from N, VC40 to S, VC40

AR_PND_ORG_CCRD_T

Fixed the primary key – took out space in fdoc_nbr field name by removing field from key, hitting ok, and then going back in and adding that field back in

CA_PNDICRTHRSHLD_T

Removed last 2 fields in primary key, hit ok, then re-added same 2 fields

INTEGER_FIELD.FP_CURRENCY_DTL_T

Changed from I15 to I4

TRN_ENTR_SEQ_NBR.LD_LDGR_ENTR_T

Changed from I5 to I4

Make any manual changes required

After some analysis of the reconciliation reports, all the different datatypes and uniface packing codes, it was determined that a few situations should try to be avoided and/or corrected. The following changes affected so few definitions that they could be made manually in the uniface editor. To find the number of entries these affect, query the database table holding all of the uniface definitions (uniface_defs). Make the changes in the Uniface model define entity area.

Described as Datatype, Packing Code (ex. N, C1 is N datatype and C1 packing code)

tinyint types: change N,C1 to N,I1
smallint types: change N,F to N,I2
timestamp types: change S,VR to S,VR8

varbinary types: change R,R* to R,SR*

Create and load the uniface_defs2 table (new definitions)

A second table is needed to hold the new uniface models so queries can be run against them. This table is the exact same as uniface_defs. Create UNIFACE_DEFS2 (follow instructions from reconciliation).

Load the table the same way, but after the data is loaded, remove everything except the new models.

We need to confirm that there are no more old definitions (that will generate any of the undesired datatypes in Oracle, i.e. C, or M2)

Find any fields that are still defined as C (should be VC):

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND LIKE 'C%' (found 17)

There are some left because the #I Cx was split onto 2 lines #I and on one and Cx on the next. Also because some were #I !Cx when unloaded (the ! was stripped out on the load back in).

Manually fix the problems.

MODEL ENTITY FIELD DA SHORTHAND I

-------------------------------- -------------------------------- -------------------------------- -- ---------- -

ODS_IU SF_ARRAY AMOUNT N C20 Y

ODS_CAM CM_CACMP_RPRHIST_T CACMP_RPRSOLN_DESC S C255 Y

ODS_CAM CM_MULT_AST_HDR_T CG_AGENCY_NBR S C5 Y

ODS_IU CM_CPTLAST_HDR_T CG_AGENCY_NBR S C5 Y

ODS_CAM CM_EQPLNRTRN_DOC_T CPTLAST_INS_CD N C4 Y

ODS_CAM CM_INS_T CPTLAST_INS_CD N C4 Y

ODS_IU CM_INS_T CPTLAST_INS_CD N C4 Y

ODS_IU CM_MVBL_AST_T CPTLAST_INS_CD N C4 Y

ODS_CAM CM_AST_RETIREDOC_T CSHRCPT_FS_ORIG_CD S C2 Y

ODS_CAM CM_AST_PAYMENT_T DEPR1_REMAINDER N C20 N

ODS_IU CM_AST_PAYMENT_T DEPR1_REMAINDER N C20 N

ODS_CAM CM_AST_PAYMENT_T DEPR2_REMAINDER N C20 N

ODS_IU CM_AST_PAYMENT_T DEPR2_REMAINDER N C20 N

ODS_CAM CM_CPTLAST_OBJ_T DEPR_EXP_FOBJ_CD S C4 Y

ODS_IU FP_COIN_DTL_T INTEGER_FIELD N C20 N

ODS_IU LD_BCN_FND_LOCK_T UNIV_FISCAL_YR N C4 Y

ODS_IU LD_BCN_OBJT_DUMP_T UNIV_FISCAL_YR N C4 Y

Fix any rows that are returned from these queries:

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND LIKE 'C%' (found 17)

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND = 'M1'

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND = 'M2' (Found 71)

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND = 'M6'

SELECT * FROM UNIFACE_DEFS2 WHERE SHORTHAND LIKE 'SC%'

AND SHORTHAND != 'SC*'

Analyze the model

In order to generate the DDL for the new models, each model must be analyzed:

Uniface – Construction – Model – select desired Model – menubar Edit – Analyze Model

Note any problems encountered by going to Uniface – menubar View – Message Frame

(Disregard any "1065 - Field xxx of entity yyy might be truncated on output to DBMS.)

(Disregard any "1070 - No keys found for entity xxxx " messages.)

(Disregard any "1077 - Key field xxxx is not contiguous" messages.)

Create New Oracle Tables

Create the Oracle DDL

Uniface can create one large DDL file to define every table within a model. This should be done for each of the new models created.

Uniface – Deployment – Create Tables

Choose Oracle from the list of databases

Choose the model from the list of models

Add all of the tables by hitting the Add button as many times as necessary

(Once the focus is on the Add button, can simply hold down enter key to select all tables.)

Type in a file name to create locally on your PC (use the extension .DDL)

Hit the Create button

Parse the DDL and add custom specifications for the installation

This is the step that will split the DDL for each object into it’s own file. It also puts in the desired custom additions to the DDL (allocation to correct TABLESPACE,PUBLIC SYNONYM, GRANTS).

First, ftp the newly created DDL files to the correct unix server.

Next, we must create a mapfile for each DDL file. This is done through the gen_dss_map.pl or gen_ods_map.pl script. This is the script that will simply map what tablespaces a table and its index will reside on.

Usage: gen_tablespace_map.pl <options>

Options include:

--server <servername>

--user <username>

--password <password>

--mapfile <mapfile>

--help

--version

The mapfile is in the format: <tablename>:<tablespace for table>:<tablespace for any indexes>

Next, we must run the script to actually split out the DDL for each object into it’s own file and apply the custom specs. This is done through the parse_uniface_ddl.pl script.

--ddlfile <filename> Location of Uniface DDL file to parse.

Required.

--mapfile <filename> Location of map file to map tables/indexes

onto tablespaces. Required.

--schema <schema name> Name of the schema in which to place the

objects parsed from the ddlfile. Required.

--deftablespace <name> If no entry for the table being processed

exists in the mapfile, use this default

tablespace instead of the keyword "default".

If --deftablespace is given, so must the

argument --defindexspace. This argument is

optional. If no defaults are given for

missing map entries, the program will prompt

for missing entries.

--defindexspace <name> Same as above, except for indexes.

Optional, but required if --deftablespace is

specified.

--synonyms Create public synonyms for all tables.

Default is --nosynonyms.

--addentries If the program has prompted for missing entries,

it will attempt to add these new entries to

the mapfile. To prevent adding these entries,

override this default behavior with --noaddentries.

--help Show this help screen

--version Show RCS Id tag

The map file format is:

Comments (anything following a '#') and blank lines are allowed.

Whitespace is trimmed from the file.

The parsing script creates multiple DDL files from the parsed input

DDL file. There is one file per CREATE TABLE, one file for all

CREATE INDEX statements for a table, and one file for all

CREATE PACKAGE statements for a table. The filename conventions

are create_<tablename>.sql, create_<tablename>_idx.sql and

create_<tablename>_pkg.sql respectively. Public synonyms are added

for each table only if requested by --synonyms, and will be

written to the create_<tablename>.sql file.

IMPORTANT: DDL files are opened for append, not overwrite. So be

sure to move/delete any *.sql files from the current directory before

executing the parser.

Create the Oracle tables by executing the DDL files

To execute the DDL, pass the newly generated files to Oracle’s SQL Plus program. A script can be created to execute all of the files in one batch. Simply create a file from a listing of the directory where the DDL files are being stored.

It should be in the format: sqlplus user@SID/password @filenamegoeshere.sql

Create the Oracle view DDL by modifying Sybase view DDL

Bring the sybase view definitions into an editor and manually make the needed changes.

Outer Joins

Sybase ‘where’ clauses: Oracle ‘where’ clauses:

Field 1 *= field 2 field 1 = field 2 (+)

Field 1 =* field 2 field 1 (+) = field 2

Database names

Sybase database name qualifiers: Oracle schema names:

Fp_ods_d..table_name ods.table_name

Ca_dss_d..table_name dss.table_name

ca_dss_d.dbo.table_name dss.table_name tbd

uu_d..table_name ods.table_name tbd

gl_dss_d..table_name dss.table_name

ld_dss_d..table_name dss.table_name

ca_dss_d..table_name dss.table_name

uu_ods_d..table_name ods.table_name

User names in Create View

Sybase Create View statement: Oracle Create View statement:

Create view dbo.table_name create view table_name

Literals

Sybase syntax: Oracle Syntax

"literalx" ‘literalx’

Functions

Sybase function Oracle function

Getdate () sysdate

dateadd(dd,NN,date_field) (date_field + NN)

user_name () user

suser_name() user

(Note: the following functions appear compatible between Sybase and Oracle

lower

upper

max)

Reconcile the new Oracle definitions with the old Sybase definitions

This is the process in which the new Oracle catalog is verified against the old Sybase catalog. The process is to make a copy of the oracle catalog table and make a copy of the sybase catalog and compare the two. The comparison will be done in an Oracle table.

Sybase dss (syb_dss_tab_columns) vs Oracle dss (dss_tab_columns)

Make a copy of the Oracle catalog table and load it with the DSS data. Create an empty table in the same exact format to hold the sybase DSS catalog data.

In oracle:

create table dss_tab_columns as

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,DATA_SCALE

from all_tab_columns where owner = 'DSS'

create table syb_dss_tab_columns as select * from dss_tab_columns where 1=2;

Next, execute the following sql for each of the DSS databases and save the results in a file. This file will be run against oracle to load the syb_dss_tab_columns table.

Sybase extract: (do for each *_dss_d database)

select "insert into syb_dss_tab_columns values ('" + upper(t.name) + "', '" + upper(c.name) + "', '" + upper(x.name) + "', " + convert(char(5),c.length) + ",null,null);"

from sysobjects t, syscolumns c, systypes x

where t.type = 'U'

and t.id = c.id

and c.usertype = x.usertype

order by t.name

ar_dss_d

bcr_dss_d

ca_dss_d

ca_hist_dss_d

cg_dss_d

cm_dss_d

cm_py_rec_dss_d

gl_dss_d

gl_hist_dss_d

hu_dss_d

ld_dss_d

ps_dss_d

tr_dss_d

Save results into file to execute from oracle sqlplus to insert the rows

For duplicate table names, tables were renamed in cm_py_rec_dss_d and gl_hist_dss_d. Do

global changes on those files to reflect the renamed tables. (save as *_UPDATED)

CM_PY_REC_DSS_D

FROM TO

CB_PND_INV_ITM_GT CB_PY_PNDINVITM_GT

CM_ASSET_GT (VIEW) CM_PY_ASSET_GT

CM_ASSET_T CM_PY_ASSET_T

CM_DEPR_GT CM_PY_DEPR_GT

CM_PAYMENT_GT CM_PY_PAYMENT_GT

CM_RETIRE_GT CM_PY_RETIRE_GT

GL_DSS_D

FROM TO

GL_DETAIL_GT (VIEW) GL_HIST_DETAIL_GT

Execute the new scripts in SQLPlus by typing something similar to this:

@ar_dss_d.sql

@bcr_dss_d.sql

@ca_dss_d.sql

@ca_hist_dss_d.sql

@cg_dss_d.sql

@cm_dss_d.sql

@cm_py_rec_dss_d_UPDATED.sql

@gl_dss_d.sql

@gl_hist_dss_d.sql

@hu_dss_d.sql

@ld_dss_d.sql

@ps_dss_d.sql

@tr_dss_d.sql

It may be useful to make a backup of the data before continuing on with the next steps:

create table syb_dss_tab_columns_BACKUP as select * from syb_dss_tab_columns;

Manipulate the syb_dss_tab_columns table – convert the sybase specific values to its oracle counterpart:

update syb_dss_tab_columns

set data_type = 'VARCHAR2'

where data_type = 'CHAR';

update syb_dss_tab_columns

set data_type = 'VARCHAR2'

where data_type = 'VARCHAR';

update syb_dss_tab_columns

set data_precision = 12, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'INT';

update syb_dss_tab_columns

set data_precision = 7, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'SMALLINT';

update syb_dss_tab_columns

set data_precision = 5, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'TINYINT';

update syb_dss_tab_columns

set data_precision = 19, data_scale = 4, data_type = 'NUMBER', data_length = 22

where data_type = 'MONEY';

update syb_dss_tab_columns

set data_precision = 19, data_scale = 4, data_type = 'NUMBER', data_length = 22

where data_type = 'SMALLMONEY';

update syb_dss_tab_columns

set data_precision = null, data_scale = null, data_type = 'NUMBER', data_length = 22

where data_type = 'FLOAT';

update syb_dss_tab_columns

set data_precision = null, data_scale = null, data_type = 'NUMBER', data_length = 22

where data_type = 'REAL';

'NUMERIC', 'DECIMAL' leave as is - no way to get precision and scale from sybase

update syb_dss_tab_columns

set data_type = 'DATE', data_length = 7

where data_type in('DATETIME', 'SMALLDATETIME');

update syb_dss_tab_columns

set data_type = 'CLOB', data_length = 4000

where data_type = 'TEXT';

update syb_dss_tab_columns

set data_type = 'CHAR', data_length = 1

where data_type = 'BIT';

update syb_dss_tab_columns

set data_type = 'RAW', data_length = 8

where data_type = 'VARBINARY';

Execute the following query to compare all the matching named fields – display the non-matches:

select d.TABLE_NAME "Sybase DSS Table", d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME "Oracle DSS Table", o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from syb_dss_tab_columns d, dss_tab_columns o

where d.table_name = o.table_name

and d.column_name = o.column_name (+)

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

UNION

select d.TABLE_NAME, d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME, o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from syb_dss_tab_columns d, dss_tab_columns o

where d.table_name = o.table_name

and d.column_name (+) = o.column_name

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

many non-matching fields will show up even though they are not a problem:

All Decimal or Numeric datatypes on the Sybase side removed. (Comparison of length, precision and scale not possible.)

All percent fields going from length of 7 to length of 3 or 4.

All year fields going from length of 7 to 4.

All month fields going from length of 12 to 2.

All sequence number fields going from length of 12 to 5.

Execute the following queries to compare all the tables on each database:

1st get the number of tables from sybase for dss:

select count(distinct table_name) from syb_dss_tab_columns;

Next get the number of tables from oracle for DSS:

select count(*) from all_tables where owner ='DSS';

To get list of DSS tables that are in sybase but not in oracle:

select distinct table_name from syb_dss_tab_columns

minus

select distinct table_name from dss_tab_columns

To get list of DSS tables that are in oracle but not in sybase:

select distinct table_name from dss_tab_columns

minus

select distinct table_name from syb_dss_tab_columns

Sybase ODS (syb_ods_tab_columns) vs Oracle ODS (ods_tab_columns)

Make a copy of the Oracle catalog table and load it with the ODS data. Create an empty table in the same exact format to hold the sybase ODS catalog data.

In oracle:

create table ods_tab_columns as

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,DATA_SCALE

from all_tab_columns where owner = 'ODS'

create table syb_ods_tab_columns as select * from ods_tab_columns where 1=2;

Next, execute the following sql for each of the ODS databases and save the results in a file. This file will be run against oracle to load the syb_ods_tab_columns table.

Sybase extract: (do for each *_ods_d database)

select "insert into syb_ods_tab_columns values ('" + upper(t.name) + "', '" + upper(c.name) + "', '" + upper(x.name) + "', " + convert(char(5),c.length) + ",null,null);"

from sysobjects t, syscolumns c, systypes x

where t.type = 'U'

and t.id = c.id

and c.usertype = x.usertype

order by t.name

fp_ods_d

uu_ods_d

Save results into file to execute from oracle sqlplus to insert the rows

For duplicate table names, tables were renamed in cm_py_rec_dss_d and gl_hist_dss_d. Do

global changes on those files to reflect the renamed tables. (save as *_UPDATED)

(Renamed GL_TRANSFER_UP_T to GL_TRANSFER_UP2_T in uu_ods_d.sql – saved in uu_ods_d_UPDATED.sql)

Execute the new scripts in SQLPlus by typing something similar to this:

@"fp_ods_d.sql"

@"uu_ods_d_UPDATED.sql"

It may be useful to make a backup of the data before continuing on with the next steps:

create table syb_ods_tab_columns_BACKUP as select * from syb_ods_tab_columns;

Manipulate the syb_dss_tab_columns table – convert the sybase specific values to its oracle counterpart:

update syb_ods_tab_columns

set data_type = 'VARCHAR2'

where data_type = 'CHAR';

update syb_ods_tab_columns

set data_type = 'VARCHAR2'

where data_type = 'VARCHAR';

update syb_ods_tab_columns

set data_precision = 12, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'INT';

update syb_ods_tab_columns

set data_precision = 7, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'SMALLINT';

update syb_ods_tab_columns

set data_precision = 5, data_scale = 0, data_type = 'NUMBER', data_length = 22

where data_type = 'TINYINT';

update syb_ods_tab_columns

set data_precision = 19, data_scale = 4, data_type = 'NUMBER', data_length = 22

where data_type = 'MONEY';

update syb_ods_tab_columns

set data_precision = 19, data_scale = 4, data_type = 'NUMBER', data_length = 22

where data_type = 'SMALLMONEY';

update syb_ods_tab_columns

set data_precision = null, data_scale = null, data_type = 'NUMBER', data_length = 22

where data_type = 'FLOAT';

update syb_ods_tab_columns

set data_precision = null, data_scale = null, data_type = 'NUMBER', data_length = 22

where data_type = 'REAL';

'NUMERIC', 'DECIMAL' leave as is - no way to get precision and scale from sybase

update syb_ods_tab_columns

set data_type = 'DATE', data_length = 7

where data_type in('DATETIME', 'SMALLDATETIME');

update syb_ods_tab_columns

set data_type = 'CLOB', data_length = 4000

where data_type = 'TEXT';

update syb_ods_tab_columns

set data_type = 'CHAR', data_length = 1

where data_type = 'BIT';

update syb_ods_tab_columns

set data_type = 'RAW', data_length = 8

where data_type = 'VARBINARY';

(may be more sybase data types that are not listed here)

Execute the following query to compare all the matching named fields – display the non-matches:

select d.TABLE_NAME "Sybase ODS Table", d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME "Oracle ODS Table", o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from syb_ods_tab_columns d, ods_tab_columns o

where d.table_name = o.table_name

and d.column_name = o.column_name (+)

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

UNION

select d.TABLE_NAME, d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME, o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from syb_ods_tab_columns d, ods_tab_columns o

where d.table_name = o.table_name

and d.column_name (+) = o.column_name

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

many non-matching fields will show up even though they are not a problem:

All Decimal or Numeric datatypes on the Sybase side removed. (Comparison of length, precision and scale not possible.)

All year fields going from length of 7 to 4.

All sequence number fields going from length of 12 to 5.

All so-called "CLOB" fields on the sybase side (most were text and are going to varchar2)

All timestamp datatypes on the sybase side – as long as they map to Oracle Raw(8)

All percent fields going from length of 7 to length of 3 or 4.

All month fields going from length of 12 to 2.

Execute the following queries to compare all the tables on each database:

1st get the number of tables from sybase for ods:

select count(distinct table_name) from syb_ods_tab_columns;

Next get the number of tables from oracle for ODS:

select count(*) from all_tables where owner ='ODS';

To get list of ODS tables that are in sybase but not in oracle:

select distinct table_name from syb_ods_tab_columns

minus

select distinct table_name from ods_tab_columns

At this point, we can determine that there are about 40 tables that are in the ODS databases, but do not have a corresponding uniface entity definition in any model. So, In order to create the tables in Oracle, we will use the same method we used to get the DSS tables defined to Uniface.

Create those tables: (follow directions for loading dss tables)

-Create ODSLOAD model in Uniface

-Load Definitions – using list – just choose the missing tables (Start with fp_ods_d – if not in that database, must be in uu_ods_d)

-Unload the CASE file: ODSLOAD.cif

-Edit the file and make the necessary global changes (just like the dss model changes) and save as new file ODS_MISSING.cif

-Load the new CASE file in to the new model ODS_MISSING.cif

-Add dummy key for every table that doesn’t have an index: (manually went thru all in uniface)

AR_PND_CUST_EXT_T, FP_COA_SYNC_T, PY_SUBACCTG_DIST_MT, CB_INV_ITEM_TEMP_T, CB_ITEMDESC_TEMP_T, CB_PNDPONTE_TEMP_T, CB_PND_AST_TEMP_T, CB_PND_INV_TEMP_T, CB_PND_PO_TEMP_T, CM_LOAD_TAG_MT, CM_PRTG_TEMP_T, CR_DOCS_IN_V, FP_DOC_HEADER_BC_T, FP_TRANSFER_UP_T_SAVE, GL_GLBL_BC_TEMP_MT, LD_CSFT_BC_TEMP_MT, LD_CSFT_TEMP_T, LD_LDGR_BAL_DEL_MT, LD_LLBL_TEMP_T, LD_LLEN_DUPS_SEP19_T, LD_LLEN_DUPS_T, LD_LLEN_TEMP_T, LD_PNDBC_APPTFND_T_TEMP, LD_POSI_TEMP_T, LD_SOF_RANK_T, LD_UPDATE_POSITION_T, SYSLOGINS_SAVE, T_DBS_T, CM_ROOM_CHANGES_T

-Created DDL for all tables in ODS_MISSING model (except for gl_transfer_up_t – need to rename that one)

-Add the ODSLOAD model definitions to the uniface_defs table

-Add the ODS_MISSING model definitions to the uniface_defs2 table.

To get list of ODS tables that are in oracle but not in sybase:

select distinct table_name from ods_tab_columns

minus

select distinct table_name from syb_ods_tab_columns

drop those tables - they are the extra definitions in the uniface models that

are not used in the ODS databases.

Manually created a file to drop those tables. "drop_non_ods_tabs.sql"

Get list of tables that are defined in more than 1 model. (Means that DDL was generated twice for these tables.) Go thru those table definitions in uniface and compare to the sybase definition - determine which model has the correct layout.

select distinct entity

from uniface_defs2 u

where model != 'DSS'

and 1 < (select count(distinct model)

from uniface_defs2

where model != 'DSS'

and entity = u.entity)

(Because the script that parses the DDL overwrites the first table’s DDL with the second same-named table’s DDL, need to ensure the correct layout is applied. Generate the correct DDL from the appropriate models determined above. Since PEND_INVOICE_ITEMT definition in both models doesn’t match the sybase ods definition, CHANGED THE ODS_CAM model to match the sybase fp_ods_d layout of PEND_INVOICE_ITEMT (just had to remove 2 fields: DISP_ITEM_COST and DISP_ITEM_TOTAL)

Created DDL-ODScorrect_ddl_for_dups.sql

Oracle ODS vs. Oracle DSS

Compare any same named tables in the ODS vs DSS. Execute the following query to determine if any of the table layouts do not match.

select d.TABLE_NAME "Oracle DSS Table", d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME "Oracle ODS Table", o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from DSS_tab_columns d, ods_tab_columns o

where d.table_name = o.table_name

and d.column_name = o.column_name (+)

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

UNION

select d.TABLE_NAME, d.COLUMN_NAME, substr(d.DATA_TYPE,1,15), d.DATA_LENGTH, d.DATA_PRECISION, d.DATA_SCALE, '|',

o.TABLE_NAME, o.COLUMN_NAME, substr(o.DATA_TYPE,1,15), o.DATA_LENGTH, o.DATA_PRECISION, o.DATA_SCALE

from DSS_tab_columns d, ods_tab_columns o

where d.table_name = o.table_name

and d.column_name (+) = o.column_name

and d.DATA_TYPE || d.DATA_LENGTH || d.DATA_PRECISION ||d.DATA_SCALE

!= o.DATA_TYPE || o.DATA_LENGTH || o.DATA_PRECISION ||o.DATA_SCALE

Number of tables that have the same table name between Oracle ODS and DSS:

select count(distinct o.table_name) from ods_tab_columns o , dss_tab_columns d

where o.table_name = d.table_name

Transfer data from Sybase to Oracle

Kick off scripts to copy data.

load_schema.pl script.

For a specific Oracle schema, will load all the tables by accessing the Oracle catalog and starting with the first (alphabetically) table, will open a connection to sybase and start copying the data from the corresponding sybase table. Has a check in it so that if there is any data loaded in the table already, will skip that table. Also has a /semaphore subdirectory that is checked and if there already exists a file for that table (empty file with the same name as the table), then that table is skipped. Because of this logic, can kick off more than one instance of this script. Currently this script is hard-coded for the name of the Oracle schema to process.

Setup environment so that access to sybase, access to oracle, and syb/perl and ora/perl works.

Next, create the subdirectory /semaphore. Then kick off the script by typing:

./load_schema.pl load01 > load01.log 2>&1 &
bg
exit

Where the first "load01" is just a handle so you can differentiate the processes when doing a ps. The rest is the normal redirection of output to a log file called load01.log.

Output shows number of rows copied and also if there are any possible problems with the mapping of the sybase field to the oracle field.

Reconcile the new Oracle data with the old Sybase data

This is the step that will verify that the data got ported over to the Oracle database correctly.

Process to reconcile Syb DSS with Ora DSS and Syb ODS with Ora ODS

chk_rowcounts.pl script

After load_schema.pl has finished, this will go through the files placed in the /semaphore subdirectory by load_schema.pl and for every file listed there, will do a row count of the sybase table and the oracle table and will signify if they match or not.

To ensure all the numeric data is correct, create a script that will go through and sum all the rows for each numeric column.

select 'select sum(' || column_name || ') from ' || table_name || ';'

from dss_tab_columns

where data_type = 'NUMBER'

Run above for oracle, and then execute results as script

Sample:

select sum(YYYYYYYYY) from XXXXXXXXX

Add the following prefixes to the generated script for sybase:

ca_ -> ca_dss_d..ca_

cm_ -> cm_dss_d..cm_

cb_ -> cm_dss_d..cb_

cg_ -> cg_dss_d..cg_

fp_ -> bcr_dss_d..fp_

hu_ -> hu_dss_d..hu_

ld_ -> ld_dss_d..ld_

tr_ -> tr_dss_d..tr_

pend_ -> cm_py_rec_dss_d..pend_

ps_ -> ps_dss_d..ps_

tr_ -> tr_dss_d..tr_

stage3-> ca_dss_d..stage3

se_ -> tr_dss_d..se_

sum numeric columns (subset of rows)

select sum(

YYYYYYYYY

) from

XXXXXXXXX

where

YYYYYYYYY > 1000

FOR ORACLE:

select 'select ~' || table_name ||'.'|| column_name || '~, sum(' || column_name || ') from ' || table_name || ' where ' || column_name || ' > 10000;'

from dss_tab_columns

where data_type = 'NUMBER'

and column_name like '%AMT'

change ~ to ' and then execute the script in oracle

FOR SYBASE:

switch the ' to " and then execute the script in sybase

extract all char primary keys and then diff

select XXXXXXX,YYYYYYY,ZZZZZZ

from

XXXXXXXXX

SAVE AS in Sybase Central

SPOOL in Oracle

Random comparison of 10 rows:

Start with Oracle:

select * from

XXXXXXXXXXXX

where rownum in (12, 55, 87, 104, 133, 155, 177, 199, 225, 250)

Take those rows and select the same from sybase

select * from

ZZZZZZZZZZ

where

KEY1 = XXXX

KEY2 = YYYY......

To reconcile data in Oracle ODS with data in Oracle DSS

Get list of tables that are identical in DSS and ODS

select table_name from all_tables a where owner = 'DSS'

and table_name = (select table_name from all_tables where table_name = a.table_name and owner = 'ODS')

FOR ALL THOSE, Compare their rowcounts

select count(*) from

XXXXXXXXXX

sum numeric columns (all rows)

select sum(

YYYYYYYYY

) from

XXXXXXXXX

sum numeric columns (subset of rows)

select sum(

YYYYYYYYY

) from

XXXXXXXXX

where

YYYYYYYYY > 1000

Random comparison of 10 rows:

Start with Oracle:

select * from

XXXXXXXXXXXX

where rownum in (12, 55, 87, 104, 133, 155, 177, 199, 225, 250)

Take those rows and select the same

select * from

ZZZZZZZZZZ

where

KEY1 = XXXX

KEY2 = YYYY......

To transfer data from Sybase to Oracle tables using FileAid

Create FileAid Converter script for each table

FileAid Converter – menubar File –New Conversion

Source Tab (Step 1)

In Source Type drop down list, choose Sybase SQL Server System 11

In Server, type syb_dss_conv

In Source Database, type the database the table resides in

Type in UserID and Password

In Source Table/View, hit the down arrow and choose table from list (should connect)

Target Tab (Step2)

In Target Type drop down list, choose Oracle 8.x

In Target Database Alias, type the SID of the target oracle database (DBA2DEV)

Type in UserID and Password

In Target Table/View, hit the down arrow and choose table from list (should connect)

In Output mode use Append to File/Table

Map Tab (Step 3)

Right mouse click in the Target Field Expression Column

Choose Match by name from the pop-up menu

For every Sybase CHAR field that is NOT NULL, add the expression Rtrim on the Target side

(Change "[field_name_here]" to "RTrim([field_name_here])"

(Setting up an Expression Template speeds up this process)

Menubar File – Save Conversion – (save it with the same name as the table)

Menubar File – Close Conversion

Highlight the newly created conversion from the Conversion List

Menubar File – Export Conversion – (save it with the same name as the table) (.cxs file)

Transfer the FileAid Converter script to server

FTP the newly created conversion script file (.cxs file) to server

Execute the Conversion

Execute the conversion by typing "CXEngine table_name_here.cxs"