Technical.Net

DMAIC - Six Sigma

step1: Used SSMA to generate DDL for Sqlserver
- modified DDL to remove rowid (column)
- removed cascade on delete
- introduced appropriate identity column
- ran DDL script on Sqlserver

step 2: exported data with INSERT statements using Toad
- inserted the data into Sqlserver
- to get around identity issue and foreign key constraint
o introduce the following statements
- set identity_insert CONFORMANCE_SET on and off
- ALTER TABLE xx NOCHECK CONSTRAINT ALL
ALTER TABLE xx CHECK CONSTRAINT ALL

o use this to generate ALTER statements:
SELECT 'ALTER TABLE ' +
+ name + ' NOCHECK CONSTRAINT ALL'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0

step 3: convert functions
- use SSMA to generate functions
- review each function and remove SSMA specific functions (as they are not required and it introduces complexity in the sqlserver)
- function (with output parameter) has to be converted to a procedure in sql
- remove REF CURSOR
- TO_DATE(a, 'mm/dd/yyyy') -> Convert(datetime, a, 101)
- TO_DATE(a, 'dd-mm-yyyy') -> convert(datetime, a, 105)
- outer joins using (+) -> LEFT OUTER JOIN ... ON
- sequence generator statements have to either removed along with identity column insertions
- if seq gen is a must, then create table and add seq gen as column in the table and introduce the following lines to get next value from the gen.
SELECT @v_next = seq_1 from ORA_SEQ
set @v_next = @v_next+ 1
UPDATE ORA_SEQ SET seq_1= @v_next
o introduce 1 row in the table (the column values are taken from ora seq generator's current value)

- @@ROWCOUNT and @@ERROR
- 2627 error (unique constraint) will be returned to the application program. there is no way to trap that error, remove 2627 check and introduce SELECT COUNT(*) for key-already exists validation.


step 4: convert stored proc
- use SSMA to generate stored proc
- to handle packaged stored proc add login user and create stored proc under the user.
A_PACKAGE.GETVALUE in Ora will work in Sql also, if A_PACKAGE is a user and GETVALUE is created under this user.
EXEC sp_addlogin 'A_PACKAGE'
EXEC sp_adduser 'VARIABLE_PACKAGE'


- remove REF CURSOR
- *same as step 3*
- understand the logic and convert ROWNUM usage with TOP
- TOP does not support variable, so dynamic sql is the way to go. so use:
EXEC sp_executesql @sql, N'@a float(53) output, @s float(53) output ',
@a=@p_mean output, @s=@p_std output

- set NOCOUNT ON (to avoid unnecessary message back to the caller)
- use CAST or Convert function during string concatenation
- convert number(20,3) -> float
- convert number(20,0) -> numeric(20,0)
- get stored proc def using:
exec sp_helptext 'SSMA.DB_ERROR_GET_EXCEPTION_INFO'

step 5: convert application programs (vb.net)
- OUTER JOINS
- TO_DATE
- in ADO, for adNumeric parameters (add Numeric Scale and Precision) or convert it into adInt
- { call procedure (?,?,? {ref curor}) } is not valid. remove {ref cursor} part
- { call GetVal() } is not valid remove empty ()

interesting comparison.