Re: Philosophical question - Staging tables, name and location

With proper design, there's no need to hard-code the table names...

When needed, the way I qualify the tables in my RPG programs (JDE shop) is to put the library name in a JDE 'processing option' and then use RPG to dynamically construct a qualified table name to use in the SQL statement.

FromTableName = %trim($@FromLibrary) + '/' + 'Tablename';
ToTableName = %trim($@ToLibrary) + '/' + 'Tablename';

The 'processing option' values are controlled and changeable by the user or BA in the environment where the program is being executed.

;-)


"Charles Wilt" wrote in message news:mailman.1266.1390968590.25182.midrange-l@xxxxxxxxxxxx...

I don't have an ETL tool currently, so I'm looking at SQL and RPG...

What I do have is a CMS.. and a DEV environment on the same box (1
partition) as production.

So while this is ok for "production"


/*Load new data from staging to production*/
INSERT INTO

PRODLIB
IB.TABLE1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 from

STAGEL

IB.TABLE1;

In development, I'd need:
/*Load new data from staging to production*/
INSERT INTO

PROD
LIBDV.TABLE1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 from STAGELIBDV.TABLE1;

My CMS doesn't particular deal well with source that changed between
environments. :) Nor does any others I'm aware of. It does handle
automatically setting the library list as needed.

Thus:
/*Load new data from staging to production*/
INSERT INTO

TABLE1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 from STABLE1;

Charles


On Tue, Jan 28, 2014 at 5:33 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:

The data extract and load would be done using an ETL software or using
simple SQL statements:

Here is what it would look like in SQL:

/*Delete data from staging table*/
DELETE TESTLIB.TABLE1;

/*Load new data from production into Test library*/
INSERT INTO TESTLIB.TABLE1 (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 from PRODLIB.TABLE1;

Doesn't seem to terribly complex to me?

Only the data load stuff would need to have libraries defined.


-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Tuesday, January 28, 2014 4:07 PM
To: Midrange Systems Technical Discussion
Subject: Re: Philosophical question - Staging tables, name and location

Matt,

I was leaning in that direction. However, obviously there will be
processes that take data out of the staging table and put it into the
production table. If the name is the same, I'd have to qualify each name.
Which doesn't really play well with a IBM i CMS given my development and
production are on the same single LPAR box.

Charles