replicate entire table in RDBMS to SAS datamart.

[sassharp]

there is a 500M rows in RDBMS table. Eachday approximately 1000 new rows adds to the 500M rows. Out of 500M rows everyday 0 to 100 records updates  with the same old id one or two  field contents changes. Assume for 100 rows field contents changed. so now RDBMS contains 500M-100 +1000=500M and 900 rows.

Q) Need to replicate this RDBMS table as a SAS data set?

[Doc_Duke]

I'm not sure what the issue is.  If "replicate" means "copy", then a DATA/SET combination will do it.  If you just want to access the data, a LIBNAME to the RDBMS will do that.  If you want to replicate the "process" of updating the RDBMS table using SAS code, you've got a lot of work to do, particularly if you are thinking about in-place replacement.

[sassharp]

Need something like this.

http://www2.sas.com/proceedings/sugi31/157-31.pdf

[sassharp]

sorry not to mention Doc

Doc.

Lawrence Muhlbaier

[shivas]

Hi,

Are you looking for something like SCD type 2 Loader/Type 1 columns with version number.This can achieved using SCD type 2 transformation.

Thanks,

Shiva

[sassharp]

@Shivas

To be simple there is a table 1 in RDBMS. The RDBMS table is dynamic. Means everyday some new rows add to the RDBMS. I need to bring this table to SAS data mart.

For example the RDBMS table maintains customer transactions. For each transaction there is a unique id. Transaction id. amount1 adjusted amount, modified date.

transactionid   amount1 adjusted amount modified date

1                      10               0                  6/6/2012

2                       12               0                  5/6/2012

3                       4                 0                  4/6/2012

4                       2                0                    6/13/2012

This is the table status on 6/13/2012

on 06/14/2012 the status of the table is

transactionid   amount1 adjusted amount modified date

1                      10               0                  6/6/2012

2                       12               2                 6/14/2012

3                       4                 0                  4/6/2012

4                       2                0                    6/13/2012

5                      100               0                   6/14/2012

Now when you run this in DI studio you should get the RDMS table in SAS side. with 5 rows and row should be updated in SAS table.

we tried with update modify and delete, append code. code is working fine. Performance is low.

so wanted to try with DI studio.

thanks.

[shivas]

Hi,

Comm table would be you RDBMS table and SASComm would be SAS Dataset.You need to change the load techique tab in table loader as below to update/Insert(if new record comes it will insert and old record changes it will update..try this).Hope this helps.

Thanks,

Shiva

[sassharp]

@shivas

Thanks for your reply.

would you follow me here. That way I can send you direct message to you only. Just send me a request to follow me. I am already following you.

thanks.