CREATE TABLE #Data_File (Col1 varchar(8000))
GO

/*
The Data file should be loaded to a work table (GLOBAL TEMP WORK) that has 1 column.
The table must be a temp table that exists only in the context of the thread that
it is in, so that the new driver program may be called asynchronously.  Here we are doing
an INSERT for the data and the CONFIG files...in DB2, the would be just a straight load

*/

INSERT INTO #Data_File(Col1)
--      12345678901234567890123456789012345678901234567890123456789012345678901234567890
SELECT '12345.678xxxabc111223333zzzyyywwwzzxyzKaiser    Brett     6 Undercliff Terrace ' UNION ALL
SELECT '12345.678xxxabc123456789zzzyyywwwzzxyzObama     Hussien   666 Pennsylvania Ave ' UNION ALL
SELECT '12345.678xxxabc987654321zzzyyywwwzzxyzBush      George    123 Texas Ranch      '

/*
Config File

The Config File will be used to find the offsets of the data, and be used to generate 
SQL Statements that be used in set based processing to create a scrambled unload file.

*/

/*
[CONFIG TYPE]	[START]		[LENGTH]

The Config table must also only exist in the context of the thread so the driver
can be used at any time.  We will use offsets here, but there is a predefined layout
for the config file so the data can be loaded into it's own columns.  Reference the 
CONFIG FILE CREATION document. 

SSN			16			9
LNAME			39			10
FNAME			49			10
ADDR1			59			22
*/

CREATE TABLE #CONFIG (Col1 varchar(8000))
GO

INSERT INTO #CONFIG (Col1)	
--      123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
SELECT '                            SSN   00016009' UNION ALL
SELECT '                            LNAME 00039010' UNION ALL
SELECT '                            FNAME 00049010'
GO

/*
Scrambled Tables

These tables must be created using the existing scrambling Model
An Insert option or Unload Option must be available to a new driver
to either Create a loadable flat file, or INSERT the old and new values
to the table names listed below.

The flat file driver would be used to seed the tables
The INSERT option would be used to process New Data values, that do not 
yet appear in the table(s)
*/

-- SSN

CREATE TABLE SSN (SSN varchar(256), SCRAM_SSN varchar(256))
GO

-- SSN					SCRAM_SSN
INSERT INTO SSN(SSN, SCRAM_SSN)
SELECT '111223333',			'132313231'	UNION ALL
SELECT '123456789',			'291846573'	UNION ALL
SELECT '987654321',			'192837465'
GO

--LNAME

CREATE TABLE LNAME (LNAME varchar(256), SCRAM_LNAME varchar(256))
GO

-- LNAME					SCRAM_LNAME
INSERT INTO LNAME(LNAME, SCRAM_LNAME)
SELECT 'Kaiser',				'Lopez'	UNION ALL
SELECT 'Obama',				'Lee'		UNION ALL
SELECT 'Bush',				'Luchow'
GO

-- FNAME

CREATE TABLE FNAME (FNAME varchar(256), SCRAM_FNAME varchar(256))
GO


-- FNAME					SCRAM_FNAME
INSERT INTO FNAME(FNAME, SCRAM_FNAME)
SELECT 'Brett',				'Marc'	UNION ALL
SELECT 'Hussien',				'Young'	UNION ALL
SELECT 'George',				'Dieter'
GO


/*

Using the #CONFIG TABLE we can genertae the SQL statement to create a 
DB2 PARMS('SQL') Unload file

*/

SELECT SQL FROM (
SELECT 'SELECT *' AS SQL, 1 AS SQL_Group
UNION ALL
SELECT 'FROM #Data_File d' AS SQL, 2 AS SQL_Group
UNION ALL
SELECT 'LEFT JOIN ' + CONFIG_CODE 
					+ ' ON ' + CONFIG_CODE + ' = SUBSTRING(d.Col1,'
					+ CONFIG_LOCATION
					+ ','
					+ CONFIG_LENGTH
					+ ')' AS SQL, 3 AS SQL_Group
FROM (
		SELECT SUBSTRING(Col1,29,6) AS CONFIG_CODE
			 , SUBSTRING(Col1,35,5) AS CONFIG_LOCATION
			 , SUBSTRING(Col1,40,3) AS CONFIG_LENGTH
		  FROM #CONFIG
) AS XXX
) AS XXX

-- Which Produces

SELECT *
FROM #Data_File d
LEFT JOIN SSN    ON SSN    = SUBSTRING(d.Col1,00016,009)
LEFT JOIN LNAME  ON LNAME  = SUBSTRING(d.Col1,00039,010)
LEFT JOIN FNAME  ON FNAME  = SUBSTRING(d.Col1,00049,010)

/*  The above statement gives us all we need to create a scrambled file.  
We would just need to create to SELECT Statement to be a series of offsets to match 
the input and replace the existing data with the scrambled fields.

This set based method, while it may scan, will still be much faster than any I/O
intensive method that could be built with CURSORS or Sequential reads through a 
QSAM File.

In a client server model, all of these statements can be generated.  We can do the same
In DB2, but this method would require us to have the ability to do dynamic SQL 

*/