Generate Insert/Update Script for DataStage Oracle Connector Automatically

Here is an updated post.

CodeProject For transactional (non-bulk) data load in DataStage using Oracle connectors, it is always the best option to use the "update then insert" mode. As shown in this post, the "update then insert" mode is as fast as the "insert then update" mode when it is 100% inserts, and outperform other modes when updates has higher percentage. Even if you know that the incoming data is 100% inserts, "update then insert" is still a better choice. Because your job may fail in the middle, and the data has been partially loaded. Now, if you restart the job, it is no longer 100% inserts.

In order to use the "update then insert" mode, you need to specify key column(s) in an Oracle connector stage, so that it knows which columns should be matched in where clause while generating update statement at run-time. Alternatively, you can provide insert/update statements yourself.

Writing insert/update statements by hand is tedious, especially if you have a lot of tables. The following SQL scripts generate insert/update statements automatically. You may paste the output into Oracle connector stages directly. Or you may wrap the scripts in a simple parallel job, and save the output into a text file. Use a sequence job to loop through hundreds of tables.

For update statement:

	with col_info as (
	select
	c.owner,
	c.table_name,
	c.column_name,
	c.column_id,
	pc.column_name as PK_COL
	from
	all_tab_cols c join all_constraints p on
	c.owner = p.owner and
	c.table_name = p.table_name and
	p.constraint_type = 'P'
	left join all_cons_columns pc on
	p.constraint_name = pc.constraint_name and
	p.owner = pc.owner and
	c.column_name = pc.column_name
	where
	c.owner = '#OWNER#' and
	c.table_name = '#TGT_TABLE#'
	order by c.column_id
	) select
	'UPDATE #TGT_TABLE# SET ' ||
	listagg(case when pk_col is null then column_name || ' = ORCHESTRATE.' || column_name end, ', ') within group (order by column_id) ||
	' WHERE ' ||
	listagg(case when pk_col is not null then pk_col || ' = ORCHESTRATE.' || pk_col end, ' AND ') within group (order by column_id) as UPD_SQL
	from col_info

view raw generate_update.sql hosted with ❤ by GitHub

For insert statement:

	with col_info as (
	select
	c.owner,
	c.table_name,
	c.column_name,
	c.column_id,
	pc.column_name as PK_COL
	from
	all_tab_cols c join all_constraints p on
	c.owner = p.owner and
	c.table_name = p.table_name and
	p.constraint_type = 'P'
	left join all_cons_columns pc on
	p.constraint_name = pc.constraint_name and
	p.owner = pc.owner and
	c.column_name = pc.column_name
	where
	c.owner = '#OWNER#' and
	c.table_name = '#TGT_TABLE#'
	order by c.column_id
	) select
	'INSERT INTO #TGT_TABLE# (' ||
	listagg(column_name, ', ') within group (order by column_id) ||
	') VALUES (' ||
	listagg('ORCHESTRATE.' || column_name, ', ') within group (order by column_id) ||
	')' as INS_SQL
	from col_info

view raw generate_insert.sql hosted with ❤ by GitHub

ISD Code Customization Tips, Part III: Avoid Reading QueryString Directly

CodeProject

Introduction

ISD makes it trivial to implement URL parameter encryption. All you need to do is to tick a checkbox in application generation options. This is extremely helpful in scenarios when you need to turn on/off the functionality in the middle of your development. For example, your client requests to add URL encryption at the last minute before you are ready to release the application. Another scenario is that you had encryption turned on from the beginning of your project. In order to track a tricky bug, you turned encryption off temporarily. After the bug was fixed, you turned encryption back on.

However, if you read URL parameters directly in your customization code, like this,

string companyId = Page.Request.QueryString["CompanyId"];

you lose all the flexibility and convenience ISD provides. That's because ISD adds/removes encryption-related code only in auto generated section II. It does not touch custom code in section I. Therefore, it is your responsibility to switch on/off encryption in section I.

Tip

In order to read URL parameters in section I custom code without losing the encryption on/off flexibility, you can use URL( ) formula function, like this,

string companyId = EvaluateFormula("URL(\"CompanyId\")");

Formula function URL( ) automatically decrypts parameters if URL encryption is turned on. 

Age Calculation: Oracle vs. SQL Server

Oracle	months_between(sysdate, dob)/12
SQL server	year(getdate()) - year(dob) + (month(getdate()) - month(dob))/12.0 + (day(getdate()) - day(dob))/365.0

If you are only interested in the integer part of the age, here is an alternative method  for SQL server:

(cast(convert(varchar(8), getdate(), 112) as int) - cast(convert(varchar(8), dob, 112) as int))/10000

DateDiff: Oracle vs. SQL Server

CodeProject

Introduction

Oracle and SQL server are very different on calculating the difference between two dates, both syntactically and semantically. The following table summarizes their differences.

	Syntax	Semantics
Oracle	date2 - date1	Time elapsed from date1 to date2 measured in days.
SQL Server	datediff(unit, date1, date2)	The number of date/time boundaries crossed from date1 to date2.

The SQL server's semantics is best demonstrated with the following example.

	select
	datediff(s, from_date, to_date) sec_diff,
	datediff(mi, from_date, to_date) min_diff,
	datediff(hh, from_date, to_date) hr_diff,
	datediff(d, from_date, to_date) day_diff,
	datediff(m, from_date, to_date) mon_diff,
	datediff(yy, from_date, to_date) yr_diff
	from (values
	(cast('2000-12-31 00:00:05' as datetime), cast('2000-12-31 00:00:15' as datetime)),
	(cast('2000-12-31 23:59:55' as datetime), cast('2001-01-01 00:00:05' as datetime))
	) as ts(from_date, to_date);

view raw DateDiff.sql hosted with ❤ by GitHub

In both cases, the time elapsed is the same, 10 seconds. But the boundary counts are different. Be careful about the semantic difference. Make sure what you calculate is what you want.

Now it begs the question: how to calculate boundaries crossed in Oracle or time elapsed in SQL server.

Boundaries crossed in Oracle

First, use trunc() function to align dates to their boundaries. Then calculate the difference. For example,

	with dates as (
	select to_date('31-dec-2000 00:00:05') from_date, to_date('31-dec-2000 00:00:15') to_date from dual
	union all
	select to_date('31-dec-2000 23:59:55') from_date, to_date('01-jan-2001 00:00:05') to_date from dual
	) select
	round((trunc(to_date, 'MI') - trunc(from_date, 'MI')) * 24 * 60) as min_cross,
	round((trunc(to_date, 'HH') - trunc(from_date, 'HH')) * 24) as hr_cross,
	trunc(to_date, 'DD') - trunc(from_date, 'DD') as day_cross
	from
	dates;

view raw oracle_date_boundary.sql hosted with ❤ by GitHub

Time elapsed in SQL server

Use second boundaries crossed as an approximation to seconds elapsed. Then convert it to other unit. For example,

	select
	datediff(s, from_date, to_date) sec_diff,
	datediff(mi, from_date, to_date) min_crossed,
	datediff(s, from_date, to_date)/60.0 min_elapsed,
	datediff(hh, from_date, to_date) hr_cross,
	datediff(s, from_date, to_date)/3600.0 hr_elapsed
	from (values
	(cast('2000-12-31 00:00:00' as datetime), cast('2000-12-31 00:59:59' as datetime)),
	(cast('2000-12-31 23:59:59' as datetime), cast('2001-01-01 00:59:58' as datetime))
	) as ts(from_date, to_date);

view raw time_elapsed_sql_svr.sql hosted with ❤ by GitHub

Automatically Create DataStage Schema Files

Introduction

Loading a csv file to a database table is one of the simplest DataStage tasks. Using Runtime Column Propagation (RCP), a single parameterized job can handle multiple files. Here is a step-by-step tutorial to do just that: Guidelines to Develop a Generic Job using Schema Files. For each csv file, DataStage needs a schema file to load column information at runtime. In the tutorial, a sample schema file is created by hand. If there are dozens of csv files to be loaded, creating all those schema files manually is quite tedious. Since every csv file is mirrored in a database table, it is possible to let DataStage collect column information from the database automatically. I will show you 3 approaches in this post.

Semi-Automated Solution

The first solution is semi-automatic, using DataStage's Import Table Definition wizard. In DataStage designer, click menu item Import / Table Definitions / Orchestrate Schema Definitions, and then select Database table (via orchdbutil) option. Type in database connection info, and select the table to be imported. In the next step, DataStage reads column information from the database, and displays it in the schema file format, as shown below. 

Copy and paste the columns to a schema template. The template contains other format information, such as field and record delimiters. Save it to a new text file, and this table is done. Go back to the previous step, select another table, and repeat.
This approach works well on a handful of tables. If there are a lot more tables, or table structures change frequently, a fully automated approach is more desirable.

Fully Automated Solution 1

As revealed in the previous approach, the DataStage import wizard uses a command-line application (orchdbutil) to generate the schema under the hood. If you have access to the DataStage server and are comfortable with shell scripting or other scripting language, you can write a script to loop through table names in an input file, feed the table to orchdbutil, and parse its output. I am not going to get into the details. Here is a blog post that can help you get started: Importing Table Definitions With OrchDbUtil.

Fully Automated Solution 2

Another fully automated approach takes advantage of DataStage itself. Create a parallel job to read schema  through an Oracle connector using a custom SQL statement, and save it to a sequential file. Create a sequence job to loop through a list of tables. Below is a sample implementation of the parallel job, the SQL statement and the sequence job.

	select 'record {final_delim=end, record_delim_string=''\n'', delim=''|'', quote=none} (' as col_scm from dual
	union all
	select * from (
	SELECT
	COLUMN_NAME || ':'
	|| CASE WHEN NULLABLE = 'Y' THEN 'nullable ' ELSE '' END
	|| CASE DATA_TYPE
	WHEN 'NUMBER' THEN 'decimal[' || DATA_PRECISION || ',' || DATA_SCALE || ']'
	WHEN 'TIMESTAMP' THEN 'timestamp'
	WHEN 'DATE' THEN 'date'
	WHEN 'VARCHAR2' THEN 'string[max=' || DATA_LENGTH || ']'
	WHEN 'CHAR' THEN 'string[' || DATA_LENGTH || ']'
	ELSE 'Unknown data type' END
	|| CASE WHEN NULLABLE = 'Y' THEN ' {null_field=''''}' END
	|| ';' as COL_SCM
	FROM
	ALL_TAB_COLS
	WHERE
	OWNER = '#SCHEMA_NAME#' AND
	TABLE_NAME = '#TGT_TABLE#'
	order by column_id
	)
	union all
	select ')' from dual

view raw auto_schema.sql hosted with ❤ by GitHub