Re: Philosophical question - Staging tables, name and location

["TheBorg" <sjl_abc@xxxxxxxxxxx>]

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