Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Staging area in ASE

15 posts in General Discussion Last posting was on 2009-06-22 19:09:16.0Z
Ignacio Vera Posted on 2009-06-17 09:47:28.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a38bbb0.77ac.1681692777@sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 17 Jun 2009 02:47:28 -0700
X-Trace: forums-3-dub.sybase.com 1245232048 10.22.241.188 (17 Jun 2009 02:47:28 -0700)
X-Original-Trace: 17 Jun 2009 02:47:28 -0700, forums-3-dub.sybase.com
Lines: 32
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27875
Article PK: 77124

Dear group,

We are running an operational data store (ODS) in Sybase IQ.
We recieve daily a considerable amount of data to insert
into the data store( aprox. 3 million rows a day). Due to
the nature of IQ we would like to insert the data into
batches and to implement a staging area in Sybase ASE to
hold the data between batches.

When data is moved to ODS, I would like to truncate the
table instead of delete. This is more efficient and it will
avoid problems like filling up the transaction log.
Potencially we want to delete million of rows.

The process to move data into the ODS cannot be instrusive,
therefore new data arriving when processing a batch should
be able to be inserted into the staging area. That means
that the data to be processed needs to be separated from the
new data arriving. I thought in using two tables and a view
which will be pointing to one of those tables at a time.
Unfortunely ASE does not support alter views.

Does anyone has experience with an implementation of this
sort?

Regards,

I.

Regards,

Ignacio


"Mark A. Parsons" <iron_horse Posted on 2009-06-17 13:37:45.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
References: <4a38bbb0.77ac.1681692777@sybase.com>
In-Reply-To: <4a38bbb0.77ac.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090616-0, 06/16/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a38f1a9$1@forums-3-dub.sybase.com>
Date: 17 Jun 2009 06:37:45 -0700
X-Trace: forums-3-dub.sybase.com 1245245865 10.22.241.152 (17 Jun 2009 06:37:45 -0700)
X-Original-Trace: 17 Jun 2009 06:37:45 -0700, vip152.sybase.com
Lines: 76
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27876
Article PK: 77126

With the 2-table/view setup it sounds like there is a point in time when you know that you would be switching tables
(eg, when would the IQ feed know to start processing a new batch of data from a new/different table?). Wouldn't it just
be part of your 'switch' logic that you drop/create the view?

Another option would be to have multiple tables in the ASE database. Each table would have a unique name, perhaps
adding a datetime string to each table name (eg, data_20090617_0900 ==> 17 June 2009 09:00). These tables would be
created/dropped as needed. From here you could have your various processes designed to access the differently named
tables ... or you could drop/create views each time a new table is created (ie, a variation on the 2-table/view setup).

---------------

If you're using ASE 15.x and have a license for partitioned tables then you could use a partitioned table to manage your
data. This would eliminate the requirement for modifying a view definition ... though all of your processes would need
to be designed to insure they access the appropriate partition (eg, make sure the partition key is referenced in the
WHERE clause of all queries).

One option would be to have 2 partitions, perhaps with the partition key being a smallint. Your various processes could
access the appropriate partition by providing the associated partition id (eg, id=1 is currently used for incoming raw
data while id=2 is used for outgoing IQ-feed processing; when it comes time to switch then you use id=2 for incoming raw
data and id=1 for outgoing IQ-feed processing). The idea is to toggle between the partitions by using the appropriate
id value. Obviously you'll need a control process to insure all processes are accessing the correct partition at all
times. When it's time to toggle/switch the partitions you can use the 'truncate table ... partition' command to
truncate the desired partition.

Another partitioning option would be to design your table to use range partitions (eg, range based on datetime). Each
time you need a 'new table' you just 'alter table/add partition'. When you no longer need an 'old table' you can 'alter
table/drop partition'. The general idea is to use an ever-increasing datetime stamp as your range key, and just
add/remove partitions as needed.

With a partitioned table there's no need to drop/create views ... just make sure your various processes are use an
appropriate partition key in all queries that access the partitioned table.

---------------

All of the above scenarios require some 'smarts' in the various processes to know when to switch ... whether it be a
'drop/create view', or an 'alter table/add-drop partition' and/or a change in the WHERE clause conditions. From your
description it sounds like you already have some idea as to when you'll be making the switch (ie, we're done IQ-feed
processing so let's start working on the next batch of data), so it just becomes a case of programming that 'switch'
logic into all of your processes.

Ignacio Vera wrote:
> Dear group,
>
> We are running an operational data store (ODS) in Sybase IQ.
> We recieve daily a considerable amount of data to insert
> into the data store( aprox. 3 million rows a day). Due to
> the nature of IQ we would like to insert the data into
> batches and to implement a staging area in Sybase ASE to
> hold the data between batches.
>
> When data is moved to ODS, I would like to truncate the
> table instead of delete. This is more efficient and it will
> avoid problems like filling up the transaction log.
> Potencially we want to delete million of rows.
>
> The process to move data into the ODS cannot be instrusive,
> therefore new data arriving when processing a batch should
> be able to be inserted into the staging area. That means
> that the data to be processed needs to be separated from the
> new data arriving. I thought in using two tables and a view
> which will be pointing to one of those tables at a time.
> Unfortunely ASE does not support alter views.
>
> Does anyone has experience with an implementation of this
> sort?
>
> Regards,
>
> I.
>
> Regards,
>
> Ignacio


Ignacio Vera Posted on 2009-06-17 15:24:32.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a390ab0.3328.1681692777@sybase.com>
References: <4a38f1a9$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 17 Jun 2009 08:24:32 -0700
X-Trace: forums-3-dub.sybase.com 1245252272 10.22.241.188 (17 Jun 2009 08:24:32 -0700)
X-Original-Trace: 17 Jun 2009 08:24:32 -0700, forums-3-dub.sybase.com
Lines: 120
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27877
Article PK: 77127

Thanks for your quick reply. The partition idea looks very
interesting but we are still using ASE 12.5.3 so we are not
using partitions. There are plans to upgrade to ASE 15 but
thing are moving slow.

The problem dropping and creating a view is that I am
getting the following error:

"Cannot run query--referenced object (name NAME NOT
RECOVERABLE) dropped during query optimization."

Any ideas?

> With the 2-table/view setup it sounds like there is a
> point in time when you know that you would be switching
> tables (eg, when would the IQ feed know to start
> processing a new batch of data from a new/different
> table?). Wouldn't it just be part of your 'switch' logic
> that you drop/create the view?
>
> Another option would be to have multiple tables in the ASE
> database. Each table would have a unique name, perhaps
> adding a datetime string to each table name (eg,
> data_20090617_0900 ==> 17 June 2009 09:00). These tables
> would be created/dropped as needed. From here you could
> have your various processes designed to access the
> differently named tables ... or you could drop/create
> views each time a new table is created (ie, a variation on
> the 2-table/view setup).
>
> ---------------
>
> If you're using ASE 15.x and have a license for
> partitioned tables then you could use a partitioned table
> to manage your data. This would eliminate the
> requirement for modifying a view definition ... though all
> of your processes would need to be designed to insure
> they access the appropriate partition (eg, make sure the
> partition key is referenced in the WHERE clause of all
> queries).
>
> One option would be to have 2 partitions, perhaps with the
> partition key being a smallint. Your various processes
> could access the appropriate partition by providing the
> associated partition id (eg, id=1 is currently used for
> incoming raw data while id=2 is used for outgoing IQ-feed
> processing; when it comes time to switch then you use id=2
> for incoming raw data and id=1 for outgoing IQ-feed
> processing). The idea is to toggle between the partitions
> by using the appropriate id value. Obviously you'll need
> a control process to insure all processes are accessing
> the correct partition at all times. When it's time to
> toggle/switch the partitions you can use the 'truncate
> table ... partition' command to truncate the desired
> partition.
>
> Another partitioning option would be to design your table
> to use range partitions (eg, range based on datetime).
> Each time you need a 'new table' you just 'alter
> table/add partition'. When you no longer need an 'old
> table' you can 'alter table/drop partition'. The
> general idea is to use an ever-increasing datetime stamp
> as your range key, and just add/remove partitions as
> needed.
>
> With a partitioned table there's no need to drop/create
> views ... just make sure your various processes are use an
> appropriate partition key in all queries that access the
> partitioned table.
>
> ---------------
>
> All of the above scenarios require some 'smarts' in the
> various processes to know when to switch ... whether it be
> a 'drop/create view', or an 'alter table/add-drop
> partition' and/or a change in the WHERE clause
> conditions. From your description it sounds like you
> already have some idea as to when you'll be making the
> switch (ie, we're done IQ-feed processing so let's start
> working on the next batch of data), so it just becomes a
> case of programming that 'switch' logic into all of your
> processes.
>
>
>
>
> Ignacio Vera wrote:
> > Dear group,
> >
> > We are running an operational data store (ODS) in Sybase
> > IQ. We recieve daily a considerable amount of data to
> > insert into the data store( aprox. 3 million rows a
> > day). Due to the nature of IQ we would like to insert
> > the data into batches and to implement a staging area in
> > Sybase ASE to hold the data between batches.
> >
> > When data is moved to ODS, I would like to truncate the
> > table instead of delete. This is more efficient and it
> > will avoid problems like filling up the transaction log.
> > Potencially we want to delete million of rows.
> >
> > The process to move data into the ODS cannot be
> > instrusive, therefore new data arriving when processing
> > a batch should be able to be inserted into the staging
> > area. That means that the data to be processed needs to
> > be separated from the new data arriving. I thought in
> > using two tables and a view which will be pointing to
> > one of those tables at a time. Unfortunely ASE does not
> > support alter views.
> > Does anyone has experience with an implementation of
> > this sort?
> >
> > Regards,
> >
> > I.
> >
> > Regards,
> >
> > Ignacio


"Mark A. Parsons" <iron_horse Posted on 2009-06-17 15:53:19.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
References: <4a38f1a9$1@forums-3-dub.sybase.com> <4a390ab0.3328.1681692777@sybase.com>
In-Reply-To: <4a390ab0.3328.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090616-0, 06/16/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a39116f@forums-3-dub.sybase.com>
Date: 17 Jun 2009 08:53:19 -0700
X-Trace: forums-3-dub.sybase.com 1245253999 10.22.241.152 (17 Jun 2009 08:53:19 -0700)
X-Original-Trace: 17 Jun 2009 08:53:19 -0700, vip152.sybase.com
Lines: 164
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27878
Article PK: 77128

Without more details of what was going on at the time ... I'd guess that you had a query trying to access the view while
at the same time you were dropping/recreating the view.

If this is the case then this brings up the question of whether or not you have a period of time during which you can
make a clean switch/change in the view definition, ie, is there a brief period of time where all processing is halted
while the view is modified?

---------------------------------

Another option that may work is to have all your processing performed through a set of stored procedures.

Each procedure would actually call 1 of 2 subordinate procs based on which table the process should be accessing (eg,
subproc_1 is used to access table_1, subproc_2 is used to access table_2).

The decision as to which subordinate proc to call would be handled by an input parameter to the main/parent procs.


Which subordinate proc/table to reference could also be determined by a small configuration table that contains the
current proc/table usage (ie, update the config table instead of dropping/recreating the view). Then instead of passing
in an input parameter to the parent proc, the parent proc would simply read the current settings from the config table.

'course, you still have to have a point in time where you can make a clean switch (ie, update the config table, truncate
the old data table, and not have any processing going on at the time).

---------------------------------

Another variation on the stored proc method ... no need for subordinate procs, but the main/parent proc would build
dynamic SQL to access the desired table. Said dynamic SQL would then be submitted to an exec() construct.

Which table to use? Again, either pass this in as a stored proc parameter, or pull from a config table.

Downside: If you have a lot of queries then you a) will need to modify a lot of code and b) would incur some excess
overhead for query compilations.

Again, you'd still have to have a point in time where you can make a clean switch (ie, update the config table, etc).

---------------------------------

There are lots of variations/possibilities ... but all of them rely on you having a clearly defined period of time
during which you can make a clean switch (whether you are talking about views, partitions, procs, config table values, etc).

Ignacio Vera wrote:
> Thanks for your quick reply. The partition idea looks very
> interesting but we are still using ASE 12.5.3 so we are not
> using partitions. There are plans to upgrade to ASE 15 but
> thing are moving slow.
>
> The problem dropping and creating a view is that I am
> getting the following error:
>
> "Cannot run query--referenced object (name NAME NOT
> RECOVERABLE) dropped during query optimization."
>
> Any ideas?
>
>
>> With the 2-table/view setup it sounds like there is a
>> point in time when you know that you would be switching
>> tables (eg, when would the IQ feed know to start
>> processing a new batch of data from a new/different
>> table?). Wouldn't it just be part of your 'switch' logic
>> that you drop/create the view?
>>
>> Another option would be to have multiple tables in the ASE
>> database. Each table would have a unique name, perhaps
>> adding a datetime string to each table name (eg,
>> data_20090617_0900 ==> 17 June 2009 09:00). These tables
>> would be created/dropped as needed. From here you could
>> have your various processes designed to access the
>> differently named tables ... or you could drop/create
>> views each time a new table is created (ie, a variation on
>> the 2-table/view setup).
>>
>> ---------------
>>
>> If you're using ASE 15.x and have a license for
>> partitioned tables then you could use a partitioned table
>> to manage your data. This would eliminate the
>> requirement for modifying a view definition ... though all
>> of your processes would need to be designed to insure
>> they access the appropriate partition (eg, make sure the
>> partition key is referenced in the WHERE clause of all
>> queries).
>>
>> One option would be to have 2 partitions, perhaps with the
>> partition key being a smallint. Your various processes
>> could access the appropriate partition by providing the
>> associated partition id (eg, id=1 is currently used for
>> incoming raw data while id=2 is used for outgoing IQ-feed
>> processing; when it comes time to switch then you use id=2
>> for incoming raw data and id=1 for outgoing IQ-feed
>> processing). The idea is to toggle between the partitions
>> by using the appropriate id value. Obviously you'll need
>> a control process to insure all processes are accessing
>> the correct partition at all times. When it's time to
>> toggle/switch the partitions you can use the 'truncate
>> table ... partition' command to truncate the desired
>> partition.
>>
>> Another partitioning option would be to design your table
>> to use range partitions (eg, range based on datetime).
>> Each time you need a 'new table' you just 'alter
>> table/add partition'. When you no longer need an 'old
>> table' you can 'alter table/drop partition'. The
>> general idea is to use an ever-increasing datetime stamp
>> as your range key, and just add/remove partitions as
>> needed.
>>
>> With a partitioned table there's no need to drop/create
>> views ... just make sure your various processes are use an
>> appropriate partition key in all queries that access the
>> partitioned table.
>>
>> ---------------
>>
>> All of the above scenarios require some 'smarts' in the
>> various processes to know when to switch ... whether it be
>> a 'drop/create view', or an 'alter table/add-drop
>> partition' and/or a change in the WHERE clause
>> conditions. From your description it sounds like you
>> already have some idea as to when you'll be making the
>> switch (ie, we're done IQ-feed processing so let's start
>> working on the next batch of data), so it just becomes a
>> case of programming that 'switch' logic into all of your
>> processes.
>>
>>
>>
>>
>> Ignacio Vera wrote:
>>> Dear group,
>>>
>>> We are running an operational data store (ODS) in Sybase
>>> IQ. We recieve daily a considerable amount of data to
>>> insert into the data store( aprox. 3 million rows a
>>> day). Due to the nature of IQ we would like to insert
>>> the data into batches and to implement a staging area in
>>> Sybase ASE to hold the data between batches.
>>>
>>> When data is moved to ODS, I would like to truncate the
>>> table instead of delete. This is more efficient and it
>>> will avoid problems like filling up the transaction log.
>>> Potencially we want to delete million of rows.
>>>
>>> The process to move data into the ODS cannot be
>>> instrusive, therefore new data arriving when processing
>>> a batch should be able to be inserted into the staging
>>> area. That means that the data to be processed needs to
>>> be separated from the new data arriving. I thought in
>>> using two tables and a view which will be pointing to
>>> one of those tables at a time. Unfortunely ASE does not
>>> support alter views.
>>> Does anyone has experience with an implementation of
>>> this sort?
>>>
>>> Regards,
>>>
>>> I.
>>>
>>> Regards,
>>>
>>> Ignacio


Ignacio Vera Posted on 2009-06-18 09:52:40.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3a0e68.6f65.1681692777@sybase.com>
References: <4a39116f@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 18 Jun 2009 02:52:40 -0700
X-Trace: forums-3-dub.sybase.com 1245318760 10.22.241.188 (18 Jun 2009 02:52:40 -0700)
X-Original-Trace: 18 Jun 2009 02:52:40 -0700, forums-3-dub.sybase.com
Lines: 208
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27880
Article PK: 77130

Theorically I don't have a period where I can do the change.
The application that inserts data into the staging area is
totally independent from the application that moves data
into the ODS.

I guess the best option is to create a stored procedure that
returns the current active table, and make the application
that populates the staging area to choose the table from
there.

The application that moves data to the ODS can change the
active table when it wants to move data.

The only risk I am concerned is when reading data to move to
ODS there is still a transaction which is not commited. Can
I use with isolation levels to make sure all the
transactions are commited?

> Without more details of what was going on at the time ...
> I'd guess that you had a query trying to access the view
> while at the same time you were dropping/recreating the
> view.
>
> If this is the case then this brings up the question of
> whether or not you have a period of time during which you
> can make a clean switch/change in the view definition, ie
> , is there a brief period of time where all processing is
> halted while the view is modified?
>
> ---------------------------------
>
> Another option that may work is to have all your
> processing performed through a set of stored procedures.
>
> Each procedure would actually call 1 of 2 subordinate
> procs based on which table the process should be accessing
> (eg, subproc_1 is used to access table_1, subproc_2 is
> used to access table_2).
>
> The decision as to which subordinate proc to call would be
> handled by an input parameter to the main/parent procs.
>
>
> Which subordinate proc/table to reference could also be
> determined by a small configuration table that contains
> the current proc/table usage (ie, update the config table
> instead of dropping/recreating the view). Then instead of
> passing in an input parameter to the parent proc, the
> parent proc would simply read the current settings from
> the config table.
>
> 'course, you still have to have a point in time where you
> can make a clean switch (ie, update the config table,
> truncate the old data table, and not have any processing
> going on at the time).
>
> ---------------------------------
>
> Another variation on the stored proc method ... no need
> for subordinate procs, but the main/parent proc would
> build dynamic SQL to access the desired table. Said
> dynamic SQL would then be submitted to an exec()
> construct.
>
> Which table to use? Again, either pass this in as a
> stored proc parameter, or pull from a config table.
>
> Downside: If you have a lot of queries then you a) will
> need to modify a lot of code and b) would incur some
> excess overhead for query compilations.
>
> Again, you'd still have to have a point in time where you
> can make a clean switch (ie, update the config table,
> etc).
>
> ---------------------------------
>
> There are lots of variations/possibilities ... but all of
> them rely on you having a clearly defined period of time
> during which you can make a clean switch (whether you are
> talking about views, partitions, procs, config table
> values, etc).
>
>
>
> Ignacio Vera wrote:
> > Thanks for your quick reply. The partition idea looks
> > very interesting but we are still using ASE 12.5.3 so we
> > are not using partitions. There are plans to upgrade to
> > ASE 15 but thing are moving slow.
> >
> > The problem dropping and creating a view is that I am
> > getting the following error:
> >
> > "Cannot run query--referenced object (name NAME NOT
> > RECOVERABLE) dropped during query optimization."
> >
> > Any ideas?
> >
> >
> >> With the 2-table/view setup it sounds like there is a
> >> point in time when you know that you would be switching
> >> tables (eg, when would the IQ feed know to start
> >> processing a new batch of data from a new/different
> >> table?). Wouldn't it just be part of your 'switch'
> logic >> that you drop/create the view?
> >>
> >> Another option would be to have multiple tables in the
> ASE >> database. Each table would have a unique name,
> perhaps >> adding a datetime string to each table name (eg
> , >> data_20090617_0900 ==> 17 June 2009 09:00). These
> tables >> would be created/dropped as needed. From here
> you could >> have your various processes designed to
> access the >> differently named tables ... or you could
> drop/create >> views each time a new table is created (ie,
> a variation on >> the 2-table/view setup).
> >>
> >> ---------------
> >>
> >> If you're using ASE 15.x and have a license for
> >> partitioned tables then you could use a partitioned
> table >> to manage your data. This would eliminate the
> >> requirement for modifying a view definition ... though
> all >> of your processes would need to be designed to
> insure >> they access the appropriate partition (eg, make
> sure the >> partition key is referenced in the WHERE
> clause of all >> queries).
> >>
> >> One option would be to have 2 partitions, perhaps with
> the >> partition key being a smallint. Your various
> processes >> could access the appropriate partition by
> providing the >> associated partition id (eg, id=1 is
> currently used for >> incoming raw data while id=2 is
> used for outgoing IQ-feed >> processing; when it comes
> time to switch then you use id=2 >> for incoming raw data
> and id=1 for outgoing IQ-feed >> processing). The idea is
> to toggle between the partitions >> by using the
> appropriate id value. Obviously you'll need >> a control
> process to insure all processes are accessing >> the
> correct partition at all times. When it's time to >>
> toggle/switch the partitions you can use the 'truncate >>
> table ... partition' command to truncate the desired >>
> partition. >>
> >> Another partitioning option would be to design your
> table >> to use range partitions (eg, range based on
> datetime). >> Each time you need a 'new table' you just
> 'alter >> table/add partition'. When you no longer need
> an 'old >> table' you can 'alter table/drop partition'.
> The >> general idea is to use an ever-increasing datetime
> stamp >> as your range key, and just add/remove
> partitions as >> needed.
> >>
> >> With a partitioned table there's no need to drop/create
> >> views ... just make sure your various processes are use
> an >> appropriate partition key in all queries that access
> the >> partitioned table.
> >>
> >> ---------------
> >>
> >> All of the above scenarios require some 'smarts' in the
> >> various processes to know when to switch ... whether it
> be >> a 'drop/create view', or an 'alter table/add-drop
> >> partition' and/or a change in the WHERE clause
> >> conditions. From your description it sounds like you
> >> already have some idea as to when you'll be making the
> >> switch (ie, we're done IQ-feed processing so let's
> start >> working on the next batch of data), so it just
> becomes a >> case of programming that 'switch' logic into
> all of your >> processes.
> >>
> >>
> >>
> >>
> >> Ignacio Vera wrote:
> >>> Dear group,
> >>>
> >>> We are running an operational data store (ODS) in
> Sybase >>> IQ. We recieve daily a considerable amount of
> data to >>> insert into the data store( aprox. 3 million
> rows a >>> day). Due to the nature of IQ we would like to
> insert >>> the data into batches and to implement a
> staging area in >>> Sybase ASE to hold the data between
> batches. >>>
> >>> When data is moved to ODS, I would like to truncate
> the >>> table instead of delete. This is more efficient
> and it >>> will avoid problems like filling up the
> transaction log. >>> Potencially we want to delete million
> of rows. >>>
> >>> The process to move data into the ODS cannot be
> >>> instrusive, therefore new data arriving when
> processing >>> a batch should be able to be inserted into
> the staging >>> area. That means that the data to be
> processed needs to >>> be separated from the new data
> arriving. I thought in >>> using two tables and a view
> which will be pointing to >>> one of those tables at a
> time. Unfortunely ASE does not >>> support alter views.
> >>> Does anyone has experience with an implementation of
> >>> this sort?
> >>>
> >>> Regards,
> >>>
> >>> I.
> >>>
> >>> Regards,
> >>>
> >>> Ignacio


"Mark A. Parsons" <iron_horse Posted on 2009-06-18 14:26:08.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
References: <4a39116f@forums-3-dub.sybase.com> <4a3a0e68.6f65.1681692777@sybase.com>
In-Reply-To: <4a3a0e68.6f65.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090616-0, 06/16/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3a4e80@forums-3-dub.sybase.com>
Date: 18 Jun 2009 07:26:08 -0700
X-Trace: forums-3-dub.sybase.com 1245335168 10.22.241.152 (18 Jun 2009 07:26:08 -0700)
X-Original-Trace: 18 Jun 2009 07:26:08 -0700, vip152.sybase.com
Lines: 79
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27882
Article PK: 77136

I'm not sure modifying the isolation level is going to do your ODS processing any good. I'm assuming you don't want
dirty reads ... as this could lead to 'dirty' data getting into your ODS. On the other hand your ODS process
would/could block on a data write to the staging area ... but this doesn't necessarily mean that once the blocking
disappears that there won't be more write activity to the staging area.

It seems to me that we're right back to the question of implementing some sort of switch logic in your application.

There's also the question of what happens if (for whatever reason) the ODS/ougtoing process falls behind ... does the
staging/incoming process halt and wait for the ODS/outgoing process to catch up? or can the staging/incoming process
continue doing it's thing?

Some more ramblings ...

----------------------

How about *not* re-using any tables; instead, create a new table each time you're ready to switch. Then have the
staging/incoming process perform the 'switch' logic.

The 'switch' would consist of a) creating a 'new' table for the staging/incoming process and b) notifying the
ODS/outgoing process that it can begin processing of the 'old' table.

Since the ODS/outgoing process could (theoretically) fall behind in processing ... the use of more-than-2 tables allows
the staging/incoming process to continue doing it's thing ... while allowing the ODS/outgoing process to catch up at a
later date.

It might look something like:

- create table ODS_table_list (table_name varchar(255), status char(1))

NOTE: For sake of discussion let's assume table_name is of the format data_YYYYMMDD_HHMMSS; this allows for easy
sorting of the ODS_table_list records by 'table_name asc'

NOTE: For sake of discussion let's assume status is one of 'N'ot-ready-for-processing or 'R'eady-to-process or
'C'ompleted-processing

- ODS_table_list is empty at this point in time

- staging/incoming process creates a new table (data_20090618_095510), inserts a record in ODS_table_list
(data_20090618_095510,'N') and begins processing data

- ODS/outgoing process polls ODS_table_list (top 1, status = 'R', ordered by table_name asc) for any data that is ready
to process; finds nothing to do and goes to sleep for a period of time before it wakes up and polls ODS_table_list again
(ie, the ODS/outgoing process is an infinite loop that polls ODS_table_list every XX minutes looking for new tables to
process)

- staging/incoming process finishes a set of data, updates record table in ODS_table_list (data_20090618_095510, 'R')

- when the staging/incoming process has a new set of data to process it then creates a new table (data_20090618_104517)
for processing, and creates the associated ODS_table_list record (data_20090618_104517, 'N')

- ODS/outgoing process polls ODS_table_list (top 1, status = 'R', order by table_name asc) and finds the record for
data_20090618_095510; begins processing of said data; when completed it updates ODS_table_list.status to 'C', then polls
(top 1, status = 'R', order by table_name asc) for the next table to process

NOTE: at some point assume the ODS/outgoing process falls behind for some reason (eg, ODS/outgoing process is down for
maintenance, IQ server is down for maintenance, etc), and the staging/incoming process has created/processed several
more tables, leading to the following information in ODS_table_list:

data_20090618_095510, 'C'
data_20090618_104517, 'R'
data_20090618_121734, 'R'
data_20090618_144516, 'R'
data_20090618_150900, 'R'
data_20090619_000249, 'R'
data_20090619_072342, 'N'

When the ODS/outgoing process starts up again it will poll ODS_table_list (top 1, status = 'R', order by table_name
asc), and find that data_20090618_104517 is ready for processing; when done processing it will update the record to 'C'
and poll ODS_table_list for a new record ... continuing this poll/process/update loop until it finds no more 'R' records
(ie, it catches up with the staging/incoming process).

Ignacio Vera wrote:
> The only risk I am concerned is when reading data to move to
> ODS there is still a transaction which is not commited. Can
> I use with isolation levels to make sure all the
> transactions are commited?


Sherlock, Kevin [TeamSybase] Posted on 2009-06-17 16:04:12.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a38bbb0.77ac.1681692777@sybase.com>
Subject: Re: Staging area in ASE
Lines: 43
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3913fc$1@forums-3-dub.sybase.com>
Date: 17 Jun 2009 09:04:12 -0700
X-Trace: forums-3-dub.sybase.com 1245254652 10.22.241.152 (17 Jun 2009 09:04:12 -0700)
X-Original-Trace: 17 Jun 2009 09:04:12 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27879
Article PK: 77129

Do you have, or would you buy Replication server to accomplish this?

Several techniques and frameworks have been presented in past Techwaves to
accomplish this with replication server (here is mine:
https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip ) .
Powerdesigner also helps design and code such a system with it's
"Information Liquidity Model".

<Ignacio Vera> wrote in message news:4a38bbb0.77ac.1681692777@sybase.com...
> Dear group,
>
> We are running an operational data store (ODS) in Sybase IQ.
> We recieve daily a considerable amount of data to insert
> into the data store( aprox. 3 million rows a day). Due to
> the nature of IQ we would like to insert the data into
> batches and to implement a staging area in Sybase ASE to
> hold the data between batches.
>
> When data is moved to ODS, I would like to truncate the
> table instead of delete. This is more efficient and it will
> avoid problems like filling up the transaction log.
> Potencially we want to delete million of rows.
>
> The process to move data into the ODS cannot be instrusive,
> therefore new data arriving when processing a batch should
> be able to be inserted into the staging area. That means
> that the data to be processed needs to be separated from the
> new data arriving. I thought in using two tables and a view
> which will be pointing to one of those tables at a time.
> Unfortunely ASE does not support alter views.
>
> Does anyone has experience with an implementation of this
> sort?
>
> Regards,
>
> I.
>
> Regards,
>
> Ignacio


Ignacio Vera Posted on 2009-06-18 10:09:04.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3a1240.71fe.1681692777@sybase.com>
References: <4a3913fc$1@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 18 Jun 2009 03:09:04 -0700
X-Trace: forums-3-dub.sybase.com 1245319744 10.22.241.188 (18 Jun 2009 03:09:04 -0700)
X-Original-Trace: 18 Jun 2009 03:09:04 -0700, forums-3-dub.sybase.com
Lines: 60
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27881
Article PK: 77131

Hi,

we use replication server and I actually have Power Designer
running in a Desktop.

But data is arriving in ASCII files, not extracted from
databases, therefore using replication would be too much
overhead.

I was in the presentation during the last techwave, it was
very interesting and we might use the idea in a near future
for some of our operational databases.

> Do you have, or would you buy Replication server to
> accomplish this?
>
> Several techniques and frameworks have been presented in
> past Techwaves to accomplish this with replication server
> (here is mine:
>
https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip
> ) . Powerdesigner also helps design and code such a
> system with it's "Information Liquidity Model".
>
> <Ignacio Vera> wrote in message
> > news:4a38bbb0.77ac.1681692777@sybase.com... Dear group,
> >
> > We are running an operational data store (ODS) in Sybase
> > IQ. We recieve daily a considerable amount of data to
> > insert into the data store( aprox. 3 million rows a
> > day). Due to the nature of IQ we would like to insert
> > the data into batches and to implement a staging area in
> > Sybase ASE to hold the data between batches.
> >
> > When data is moved to ODS, I would like to truncate the
> > table instead of delete. This is more efficient and it
> > will avoid problems like filling up the transaction log.
> > Potencially we want to delete million of rows.
> >
> > The process to move data into the ODS cannot be
> > instrusive, therefore new data arriving when processing
> > a batch should be able to be inserted into the staging
> > area. That means that the data to be processed needs to
> > be separated from the new data arriving. I thought in
> > using two tables and a view which will be pointing to
> > one of those tables at a time. Unfortunely ASE does not
> support alter views. >
> > Does anyone has experience with an implementation of
> > this sort?
> >
> > Regards,
> >
> > I.
> >
> > Regards,
> >
> > Ignacio
>
>


Sherlock, Kevin [TeamSybase] Posted on 2009-06-18 15:11:59.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a3913fc$1@forums-3-dub.sybase.com> <4a3a1240.71fe.1681692777@sybase.com>
Subject: Re: Staging area in ASE
Lines: 164
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3a593f@forums-3-dub.sybase.com>
Date: 18 Jun 2009 08:11:59 -0700
X-Trace: forums-3-dub.sybase.com 1245337919 10.22.241.152 (18 Jun 2009 08:11:59 -0700)
X-Original-Trace: 18 Jun 2009 08:11:59 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27883
Article PK: 77133

Ok, I understand your problem better now.

Even with the situation using replication server, there is a period of time
when the DSI connection to the staging area has to be suspended while the
data is processed into the ODS (IQ). This is very similar to the situation
you are dealing with. The DSI has to be suspended because we don't want the
DSI to be in the middle of a transaction that didn't get migrated to IQ, and
then truncate the staging tables and possibly losing data.

However, the process of suspending the DSI even for a few minutes meant that
we lost some throughput in the system now that we had to let the
transactions back up in the source system.

This was solved by using two sets of staging tables, and a "control" table
which indicated which table was the "staging" table, and which was the
"loading" table.

In short the theory is this:

You have a process which is populating the staging tables. We'll call this
the "stagefeed" application. You have a process which is retrieving
staging data and feeding the IQ system, we'll call this the "odsfeed"
application. Each table that needs to have data fed into the ODS system
will have two staging tables defined on ASE which look exactly alike, but
either have different names, different owners, or exist in two different
databases. I prefer just keeping it all in the same staging database and
using some naming convention.

Lets assume the table to send to ODS is named "employee".

The staging tables are "employee_a" and "employee_b". There is also a
control table. Here, you could either have one control table per staging
pair, or one single control table (datarows locking) for all staging pairs.

create table ods_ctl(ods_tablename char(30) not null, stagefeed_table
char(1) not null, odsfeed_table char(1) not null, last_loadtime datetime not
null, primary key (base_tablename)) lock datarows

Now, the stagefeed process works by commiting it's work in transactions
(much like a DSI replication thread does):

stagefeed psuedo code:

-------------
begin tran
select @stagefeed_table = stagefeed_table
from ods_ctl holdlock /* hold shared row lock for entire tran */
where ods_table = 'employee'
<read row(s) of data from source file>
<load data into either "_a" or "_b" table depending on @stagefeed_table
result>
commit tran /* releasing shared row lock on ods_ctl table */
--------------

So the loading process above can either be bulk load, or a stored proc call
that might branch to either _a or _b staging table based on passed in
@stagefeed_table value. Could even create two identical stored procs that
only difference is which table is inserted into. Branch on @stagefeed_table
variable to determine which proc to call.

The "odsfeed" application psuedo code would then look like:

select @odsfeed_table = odsfeed_table
from ods_ctl
where ods_table = 'employee'
<load ODS table "employee" from _a or _b stagefeed table based on
@odsfeed_table setting>
<truncate table _a or _b based on @odsfeed_table setting>
update ods_ctl -- switch tables
set stagefeed_table = odsfeed_table
, odsfeed_table = stagefeed_table
where ods_table = ' employee'
-- Above update statement requests exclusive row lock, and will wait (block)
for stagefeed application to finish possible in-flight transaction before
the switch is made
<repeat above loop once more, then sleep for n seconds>

Assuming the update is returned, then the stagefeed application must not
have a SHAREd lock on the ods_ctl table which means it's safe to switch the
table assignments. The odsfeed application always loops through two table
switches before sleeping for a configured time and repeating the whole
process.

So basic row locking is your "switching" mechanism and is transactionaly
safe that you'll get all your rows into IQ.

I've used this above technique so that I never have to suspend a DSI thread
during IQ population from a staging database.

<Ignacio Vera> wrote in message news:4a3a1240.71fe.1681692777@sybase.com...
> Hi,
>
> we use replication server and I actually have Power Designer
> running in a Desktop.
>
> But data is arriving in ASCII files, not extracted from
> databases, therefore using replication would be too much
> overhead.
>
> I was in the presentation during the last techwave, it was
> very interesting and we might use the idea in a near future
> for some of our operational databases.
>
>
>> Do you have, or would you buy Replication server to
>> accomplish this?
>>
>> Several techniques and frameworks have been presented in
>> past Techwaves to accomplish this with replication server
>> (here is mine:
>>
> https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip
>> ) . Powerdesigner also helps design and code such a
>> system with it's "Information Liquidity Model".
>>
>> <Ignacio Vera> wrote in message
>> > news:4a38bbb0.77ac.1681692777@sybase.com... Dear group,
>> >
>> > We are running an operational data store (ODS) in Sybase
>> > IQ. We recieve daily a considerable amount of data to
>> > insert into the data store( aprox. 3 million rows a
>> > day). Due to the nature of IQ we would like to insert
>> > the data into batches and to implement a staging area in
>> > Sybase ASE to hold the data between batches.
>> >
>> > When data is moved to ODS, I would like to truncate the
>> > table instead of delete. This is more efficient and it
>> > will avoid problems like filling up the transaction log.
>> > Potencially we want to delete million of rows.
>> >
>> > The process to move data into the ODS cannot be
>> > instrusive, therefore new data arriving when processing
>> > a batch should be able to be inserted into the staging
>> > area. That means that the data to be processed needs to
>> > be separated from the new data arriving. I thought in
>> > using two tables and a view which will be pointing to
>> > one of those tables at a time. Unfortunely ASE does not
>> support alter views. >
>> > Does anyone has experience with an implementation of
>> > this sort?
>> >
>> > Regards,
>> >
>> > I.
>> >
>> > Regards,
>> >
>> > Ignacio
>>
>>


Ignacio Vera Posted on 2009-06-19 12:56:29.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3b8afd.854.1681692777@sybase.com>
References: <4a3a593f@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 19 Jun 2009 05:56:29 -0700
X-Trace: forums-3-dub.sybase.com 1245416189 10.22.241.188 (19 Jun 2009 05:56:29 -0700)
X-Original-Trace: 19 Jun 2009 05:56:29 -0700, forums-3-dub.sybase.com
Lines: 196
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27886
Article PK: 77134

Thanks for the anwer,

That is exactly the solution I was trying; two staging
tables and one control table.

Holding a lock in the control table will assure me that all
the transaction will be commited when switching tables, so
no data will be lost.

One more table, the holdlock instruction has the same effect
of using isolation level 3?

Thanks again,

Ignacio

> Ok, I understand your problem better now.
>
> Even with the situation using replication server, there is
> a period of time when the DSI connection to the staging
> area has to be suspended while the data is processed into
> the ODS (IQ). This is very similar to the situation you
> are dealing with. The DSI has to be suspended because we
> don't want the DSI to be in the middle of a transaction
> that didn't get migrated to IQ, and then truncate the
> staging tables and possibly losing data.
>
> However, the process of suspending the DSI even for a few
> minutes meant that we lost some throughput in the system
> now that we had to let the transactions back up in the
> source system.
>
> This was solved by using two sets of staging tables, and a
> "control" table which indicated which table was the
> "staging" table, and which was the "loading" table.
>
> In short the theory is this:
>
> You have a process which is populating the staging tables.
> We'll call this the "stagefeed" application. You have
> a process which is retrieving staging data and feeding
> the IQ system, we'll call this the "odsfeed" application.
> Each table that needs to have data fed into the ODS
> system will have two staging tables defined on ASE which
> look exactly alike, but either have different names,
> different owners, or exist in two different databases. I
> prefer just keeping it all in the same staging database
> and using some naming convention.
>
> Lets assume the table to send to ODS is named "employee".
>
> The staging tables are "employee_a" and "employee_b".
> There is also a control table. Here, you could either
> have one control table per staging pair, or one single
> control table (datarows locking) for all staging pairs.
>
> create table ods_ctl(ods_tablename char(30) not null,
> stagefeed_table char(1) not null, odsfeed_table char(1)
> not null, last_loadtime datetime not null, primary key
> (base_tablename)) lock datarows
>
> Now, the stagefeed process works by commiting it's work in
> transactions (much like a DSI replication thread does):
>
> stagefeed psuedo code:
>
> -------------
> begin tran
> select @stagefeed_table = stagefeed_table
> from ods_ctl holdlock /* hold shared row lock for
> entire tran */
> where ods_table = 'employee'
> <read row(s) of data from source file>
> <load data into either "_a" or "_b" table depending on
> @stagefeed_table result>
> commit tran /* releasing shared row lock on ods_ctl table
> */ --------------
>
> So the loading process above can either be bulk load, or a
> stored proc call that might branch to either _a or _b
> staging table based on passed in @stagefeed_table value.
> Could even create two identical stored procs that only
> difference is which table is inserted into. Branch on
> @stagefeed_table variable to determine which proc to
> call.
>
> The "odsfeed" application psuedo code would then look
> like:
>
> select @odsfeed_table = odsfeed_table
> from ods_ctl
> where ods_table = 'employee'
> <load ODS table "employee" from _a or _b stagefeed table
> based on @odsfeed_table setting>
> <truncate table _a or _b based on @odsfeed_table setting>
> update ods_ctl -- switch tables
> set stagefeed_table = odsfeed_table
> , odsfeed_table = stagefeed_table
> where ods_table = ' employee'
> -- Above update statement requests exclusive row lock, and
> will wait (block) for stagefeed application to finish
> possible in-flight transaction before the switch is made
> <repeat above loop once more, then sleep for n seconds>
>
> Assuming the update is returned, then the stagefeed
> application must not have a SHAREd lock on the ods_ctl
> table which means it's safe to switch the table
> assignments. The odsfeed application always loops through
> two table switches before sleeping for a configured time
> and repeating the whole process.
>
> So basic row locking is your "switching" mechanism and is
> transactionaly safe that you'll get all your rows into
> IQ.
>
> I've used this above technique so that I never have to
> suspend a DSI thread during IQ population from a staging
> database.
>
>
>
>
>
>
>
>
>
>
>
>
>
> <Ignacio Vera> wrote in message
> > news:4a3a1240.71fe.1681692777@sybase.com... Hi,
> >
> > we use replication server and I actually have Power
> > Designer running in a Desktop.
> >
> > But data is arriving in ASCII files, not extracted from
> > databases, therefore using replication would be too much
> > overhead.
> >
> > I was in the presentation during the last techwave, it
> > was very interesting and we might use the idea in a near
> > future for some of our operational databases.
> >
> >
> >> Do you have, or would you buy Replication server to
> >> accomplish this?
> >>
> >> Several techniques and frameworks have been presented
> in >> past Techwaves to accomplish this with replication
> server >> (here is mine:
> >>
> >
>
https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip
> >> ) . Powerdesigner also helps design and code such a
> >> system with it's "Information Liquidity Model".
> >>
> >> <Ignacio Vera> wrote in message
> >> > news:4a38bbb0.77ac.1681692777@sybase.com... Dear
> group, >> >
> >> > We are running an operational data store (ODS) in
> Sybase >> > IQ. We recieve daily a considerable amount of
> data to >> > insert into the data store( aprox. 3 million
> rows a >> > day). Due to the nature of IQ we would like to
> insert >> > the data into batches and to implement a
> staging area in >> > Sybase ASE to hold the data between
> batches. >> >
> >> > When data is moved to ODS, I would like to truncate
> the >> > table instead of delete. This is more efficient
> and it >> > will avoid problems like filling up the
> transaction log. >> > Potencially we want to delete
> million of rows. >> >
> >> > The process to move data into the ODS cannot be
> >> > instrusive, therefore new data arriving when
> processing >> > a batch should be able to be inserted into
> the staging >> > area. That means that the data to be
> processed needs to >> > be separated from the new data
> arriving. I thought in >> > using two tables and a view
> which will be pointing to >> > one of those tables at a
> time. Unfortunely ASE does not >> support alter views. >
> >> > Does anyone has experience with an implementation of
> >> > this sort?
> >> >
> >> > Regards,
> >> >
> >> > I.
> >> >
> >> > Regards,
> >> >
> >> > Ignacio
> >>
> >>
>
>


Sherlock, Kevin [TeamSybase] Posted on 2009-06-19 14:09:02.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a3a593f@forums-3-dub.sybase.com> <4a3b8afd.854.1681692777@sybase.com>
Subject: Re: Staging area in ASE
Lines: 202
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3b9bfe@forums-3-dub.sybase.com>
Date: 19 Jun 2009 07:09:02 -0700
X-Trace: forums-3-dub.sybase.com 1245420542 10.22.241.152 (19 Jun 2009 07:09:02 -0700)
X-Original-Trace: 19 Jun 2009 07:09:02 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27887
Article PK: 77137

Yes, holdlock with retain any shared locks on that table for the duration of
your transaction.

<Ignacio Vera> wrote in message news:4a3b8afd.854.1681692777@sybase.com...
> Thanks for the anwer,
>
> That is exactly the solution I was trying; two staging
> tables and one control table.
>
> Holding a lock in the control table will assure me that all
> the transaction will be commited when switching tables, so
> no data will be lost.
>
> One more table, the holdlock instruction has the same effect
> of using isolation level 3?
>
> Thanks again,
>
> Ignacio
>
>> Ok, I understand your problem better now.
>>
>> Even with the situation using replication server, there is
>> a period of time when the DSI connection to the staging
>> area has to be suspended while the data is processed into
>> the ODS (IQ). This is very similar to the situation you
>> are dealing with. The DSI has to be suspended because we
>> don't want the DSI to be in the middle of a transaction
>> that didn't get migrated to IQ, and then truncate the
>> staging tables and possibly losing data.
>>
>> However, the process of suspending the DSI even for a few
>> minutes meant that we lost some throughput in the system
>> now that we had to let the transactions back up in the
>> source system.
>>
>> This was solved by using two sets of staging tables, and a
>> "control" table which indicated which table was the
>> "staging" table, and which was the "loading" table.
>>
>> In short the theory is this:
>>
>> You have a process which is populating the staging tables.
>> We'll call this the "stagefeed" application. You have
>> a process which is retrieving staging data and feeding
>> the IQ system, we'll call this the "odsfeed" application.
>> Each table that needs to have data fed into the ODS
>> system will have two staging tables defined on ASE which
>> look exactly alike, but either have different names,
>> different owners, or exist in two different databases. I
>> prefer just keeping it all in the same staging database
>> and using some naming convention.
>>
>> Lets assume the table to send to ODS is named "employee".
>>
>> The staging tables are "employee_a" and "employee_b".
>> There is also a control table. Here, you could either
>> have one control table per staging pair, or one single
>> control table (datarows locking) for all staging pairs.
>>
>> create table ods_ctl(ods_tablename char(30) not null,
>> stagefeed_table char(1) not null, odsfeed_table char(1)
>> not null, last_loadtime datetime not null, primary key
>> (base_tablename)) lock datarows
>>
>> Now, the stagefeed process works by commiting it's work in
>> transactions (much like a DSI replication thread does):
>>
>> stagefeed psuedo code:
>>
>> -------------
>> begin tran
>> select @stagefeed_table = stagefeed_table
>> from ods_ctl holdlock /* hold shared row lock for
>> entire tran */
>> where ods_table = 'employee'
>> <read row(s) of data from source file>
>> <load data into either "_a" or "_b" table depending on
>> @stagefeed_table result>
>> commit tran /* releasing shared row lock on ods_ctl table
>> */ --------------
>>
>> So the loading process above can either be bulk load, or a
>> stored proc call that might branch to either _a or _b
>> staging table based on passed in @stagefeed_table value.
>> Could even create two identical stored procs that only
>> difference is which table is inserted into. Branch on
>> @stagefeed_table variable to determine which proc to
>> call.
>>
>> The "odsfeed" application psuedo code would then look
>> like:
>>
>> select @odsfeed_table = odsfeed_table
>> from ods_ctl
>> where ods_table = 'employee'
>> <load ODS table "employee" from _a or _b stagefeed table
>> based on @odsfeed_table setting>
>> <truncate table _a or _b based on @odsfeed_table setting>
>> update ods_ctl -- switch tables
>> set stagefeed_table = odsfeed_table
>> , odsfeed_table = stagefeed_table
>> where ods_table = ' employee'
>> -- Above update statement requests exclusive row lock, and
>> will wait (block) for stagefeed application to finish
>> possible in-flight transaction before the switch is made
>> <repeat above loop once more, then sleep for n seconds>
>>
>> Assuming the update is returned, then the stagefeed
>> application must not have a SHAREd lock on the ods_ctl
>> table which means it's safe to switch the table
>> assignments. The odsfeed application always loops through
>> two table switches before sleeping for a configured time
>> and repeating the whole process.
>>
>> So basic row locking is your "switching" mechanism and is
>> transactionaly safe that you'll get all your rows into
>> IQ.
>>
>> I've used this above technique so that I never have to
>> suspend a DSI thread during IQ population from a staging
>> database.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> <Ignacio Vera> wrote in message
>> > news:4a3a1240.71fe.1681692777@sybase.com... Hi,
>> >
>> > we use replication server and I actually have Power
>> > Designer running in a Desktop.
>> >
>> > But data is arriving in ASCII files, not extracted from
>> > databases, therefore using replication would be too much
>> > overhead.
>> >
>> > I was in the presentation during the last techwave, it
>> > was very interesting and we might use the idea in a near
>> > future for some of our operational databases.
>> >
>> >
>> >> Do you have, or would you buy Replication server to
>> >> accomplish this?
>> >>
>> >> Several techniques and frameworks have been presented
>> in >> past Techwaves to accomplish this with replication
>> server >> (here is mine:
>> >>
>> >
>>
> https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip
>> >> ) . Powerdesigner also helps design and code such a
>> >> system with it's "Information Liquidity Model".
>> >>
>> >> <Ignacio Vera> wrote in message
>> >> > news:4a38bbb0.77ac.1681692777@sybase.com... Dear
>> group, >> >
>> >> > We are running an operational data store (ODS) in
>> Sybase >> > IQ. We recieve daily a considerable amount of
>> data to >> > insert into the data store( aprox. 3 million
>> rows a >> > day). Due to the nature of IQ we would like to
>> insert >> > the data into batches and to implement a
>> staging area in >> > Sybase ASE to hold the data between
>> batches. >> >
>> >> > When data is moved to ODS, I would like to truncate
>> the >> > table instead of delete. This is more efficient
>> and it >> > will avoid problems like filling up the
>> transaction log. >> > Potencially we want to delete
>> million of rows. >> >
>> >> > The process to move data into the ODS cannot be
>> >> > instrusive, therefore new data arriving when
>> processing >> > a batch should be able to be inserted into
>> the staging >> > area. That means that the data to be
>> processed needs to >> > be separated from the new data
>> arriving. I thought in >> > using two tables and a view
>> which will be pointing to >> > one of those tables at a
>> time. Unfortunely ASE does not >> support alter views. >
>> >> > Does anyone has experience with an implementation of
>> >> > this sort?
>> >> >
>> >> > Regards,
>> >> >
>> >> > I.
>> >> >
>> >> > Regards,
>> >> >
>> >> > Ignacio
>> >>
>> >>
>>
>>


Sherlock, Kevin [TeamSybase] Posted on 2009-06-22 14:57:42.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a3a593f@forums-3-dub.sybase.com> <4a3b8afd.854.1681692777@sybase.com> <4a3b9bfe@forums-3-dub.sybase.com>
Subject: Re: Staging area in ASE
Lines: 208
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3f9be6@forums-3-dub.sybase.com>
Date: 22 Jun 2009 07:57:42 -0700
X-Trace: forums-3-dub.sybase.com 1245682662 10.22.241.152 (22 Jun 2009 07:57:42 -0700)
X-Original-Trace: 22 Jun 2009 07:57:42 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27896
Article PK: 77145

oops. "Yes, holdlock WILL retain" ...

"Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com> wrote in
message news:4a3b9bfe@forums-3-dub.sybase.com...
> Yes, holdlock with retain any shared locks on that table for the duration
> of your transaction.
>
> <Ignacio Vera> wrote in message news:4a3b8afd.854.1681692777@sybase.com...
>> Thanks for the anwer,
>>
>> That is exactly the solution I was trying; two staging
>> tables and one control table.
>>
>> Holding a lock in the control table will assure me that all
>> the transaction will be commited when switching tables, so
>> no data will be lost.
>>
>> One more table, the holdlock instruction has the same effect
>> of using isolation level 3?
>>
>> Thanks again,
>>
>> Ignacio
>>
>>> Ok, I understand your problem better now.
>>>
>>> Even with the situation using replication server, there is
>>> a period of time when the DSI connection to the staging
>>> area has to be suspended while the data is processed into
>>> the ODS (IQ). This is very similar to the situation you
>>> are dealing with. The DSI has to be suspended because we
>>> don't want the DSI to be in the middle of a transaction
>>> that didn't get migrated to IQ, and then truncate the
>>> staging tables and possibly losing data.
>>>
>>> However, the process of suspending the DSI even for a few
>>> minutes meant that we lost some throughput in the system
>>> now that we had to let the transactions back up in the
>>> source system.
>>>
>>> This was solved by using two sets of staging tables, and a
>>> "control" table which indicated which table was the
>>> "staging" table, and which was the "loading" table.
>>>
>>> In short the theory is this:
>>>
>>> You have a process which is populating the staging tables.
>>> We'll call this the "stagefeed" application. You have
>>> a process which is retrieving staging data and feeding
>>> the IQ system, we'll call this the "odsfeed" application.
>>> Each table that needs to have data fed into the ODS
>>> system will have two staging tables defined on ASE which
>>> look exactly alike, but either have different names,
>>> different owners, or exist in two different databases. I
>>> prefer just keeping it all in the same staging database
>>> and using some naming convention.
>>>
>>> Lets assume the table to send to ODS is named "employee".
>>>
>>> The staging tables are "employee_a" and "employee_b".
>>> There is also a control table. Here, you could either
>>> have one control table per staging pair, or one single
>>> control table (datarows locking) for all staging pairs.
>>>
>>> create table ods_ctl(ods_tablename char(30) not null,
>>> stagefeed_table char(1) not null, odsfeed_table char(1)
>>> not null, last_loadtime datetime not null, primary key
>>> (base_tablename)) lock datarows
>>>
>>> Now, the stagefeed process works by commiting it's work in
>>> transactions (much like a DSI replication thread does):
>>>
>>> stagefeed psuedo code:
>>>
>>> -------------
>>> begin tran
>>> select @stagefeed_table = stagefeed_table
>>> from ods_ctl holdlock /* hold shared row lock for
>>> entire tran */
>>> where ods_table = 'employee'
>>> <read row(s) of data from source file>
>>> <load data into either "_a" or "_b" table depending on
>>> @stagefeed_table result>
>>> commit tran /* releasing shared row lock on ods_ctl table
>>> */ --------------
>>>
>>> So the loading process above can either be bulk load, or a
>>> stored proc call that might branch to either _a or _b
>>> staging table based on passed in @stagefeed_table value.
>>> Could even create two identical stored procs that only
>>> difference is which table is inserted into. Branch on
>>> @stagefeed_table variable to determine which proc to
>>> call.
>>>
>>> The "odsfeed" application psuedo code would then look
>>> like:
>>>
>>> select @odsfeed_table = odsfeed_table
>>> from ods_ctl
>>> where ods_table = 'employee'
>>> <load ODS table "employee" from _a or _b stagefeed table
>>> based on @odsfeed_table setting>
>>> <truncate table _a or _b based on @odsfeed_table setting>
>>> update ods_ctl -- switch tables
>>> set stagefeed_table = odsfeed_table
>>> , odsfeed_table = stagefeed_table
>>> where ods_table = ' employee'
>>> -- Above update statement requests exclusive row lock, and
>>> will wait (block) for stagefeed application to finish
>>> possible in-flight transaction before the switch is made
>>> <repeat above loop once more, then sleep for n seconds>
>>>
>>> Assuming the update is returned, then the stagefeed
>>> application must not have a SHAREd lock on the ods_ctl
>>> table which means it's safe to switch the table
>>> assignments. The odsfeed application always loops through
>>> two table switches before sleeping for a configured time
>>> and repeating the whole process.
>>>
>>> So basic row locking is your "switching" mechanism and is
>>> transactionaly safe that you'll get all your rows into
>>> IQ.
>>>
>>> I've used this above technique so that I never have to
>>> suspend a DSI thread during IQ population from a staging
>>> database.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> <Ignacio Vera> wrote in message
>>> > news:4a3a1240.71fe.1681692777@sybase.com... Hi,
>>> >
>>> > we use replication server and I actually have Power
>>> > Designer running in a Desktop.
>>> >
>>> > But data is arriving in ASCII files, not extracted from
>>> > databases, therefore using replication would be too much
>>> > overhead.
>>> >
>>> > I was in the presentation during the last techwave, it
>>> > was very interesting and we might use the idea in a near
>>> > future for some of our operational databases.
>>> >
>>> >
>>> >> Do you have, or would you buy Replication server to
>>> >> accomplish this?
>>> >>
>>> >> Several techniques and frameworks have been presented
>>> in >> past Techwaves to accomplish this with replication
>>> server >> (here is mine:
>>> >>
>>> >
>>>
>> https://1bosweb3.experient-inc.com/Uploads/Sybase/2007/20029.zip
>>> >> ) . Powerdesigner also helps design and code such a
>>> >> system with it's "Information Liquidity Model".
>>> >>
>>> >> <Ignacio Vera> wrote in message
>>> >> > news:4a38bbb0.77ac.1681692777@sybase.com... Dear
>>> group, >> >
>>> >> > We are running an operational data store (ODS) in
>>> Sybase >> > IQ. We recieve daily a considerable amount of
>>> data to >> > insert into the data store( aprox. 3 million
>>> rows a >> > day). Due to the nature of IQ we would like to
>>> insert >> > the data into batches and to implement a
>>> staging area in >> > Sybase ASE to hold the data between
>>> batches. >> >
>>> >> > When data is moved to ODS, I would like to truncate
>>> the >> > table instead of delete. This is more efficient
>>> and it >> > will avoid problems like filling up the
>>> transaction log. >> > Potencially we want to delete
>>> million of rows. >> >
>>> >> > The process to move data into the ODS cannot be
>>> >> > instrusive, therefore new data arriving when
>>> processing >> > a batch should be able to be inserted into
>>> the staging >> > area. That means that the data to be
>>> processed needs to >> > be separated from the new data
>>> arriving. I thought in >> > using two tables and a view
>>> which will be pointing to >> > one of those tables at a
>>> time. Unfortunely ASE does not >> support alter views. >
>>> >> > Does anyone has experience with an implementation of
>>> >> > this sort?
>>> >> >
>>> >> > Regards,
>>> >> >
>>> >> > I.
>>> >> >
>>> >> > Regards,
>>> >> >
>>> >> > Ignacio
>>> >>
>>> >>
>>>
>>>
>
>


Ignacio Vera Posted on 2009-06-22 15:10:52.0Z
Sender: 76fc.4a38bac6.1804289383@sybase.com
From: Ignacio Vera
Newsgroups: sybase.public.ase.general
Subject: Re: Staging area in ASE
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4a3f9efc.2c3.1681692777@sybase.com>
References: <4a3f9be6@forums-3-dub.sybase.com>
NNTP-Posting-Host: forums-3-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-3-dub.sybase.com
Date: 22 Jun 2009 08:10:52 -0700
X-Trace: forums-3-dub.sybase.com 1245683452 10.22.241.188 (22 Jun 2009 08:10:52 -0700)
X-Original-Trace: 22 Jun 2009 08:10:52 -0700, forums-3-dub.sybase.com
Lines: 36
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27897
Article PK: 77146

One more thing:

In your approach for the ODSFeeder:

select @odsfeed_table = odsfeed_table from ods_ctl where
ods_table = 'employee'
<load ODS table "employee" from _a or _b stagefeed table
based on @odsfeed_table setting>
<truncate table _a or _b based on @odsfeed_table setting>
update ods_ctl -- switch tables set stagefeed_table =
odsfeed_table , odsfeed_table = stagefeed_table where
ods_table = ' employee'

If I understand correctly you load the data in the loading
table before doing the switch. This means if the switch is
done once a day, you will be inserting data from the day
before, in other words, there is one day of delay in the
data in the ODS.

Would it be possible to do the ODSFeeder like:

update ods_ctl -- switch tables set stagefeed_table =
odsfeed_table , odsfeed_table = stagefeed_table where
ods_table = ' employee'
select @odsfeed_table = odsfeed_table from ods_ctl where
ods_table = 'employee'
<load ODS table "employee" from _a or _b stagefeed table
based on @odsfeed_table setting>
<truncate table _a or _b based on @odsfeed_table setting>

You first do the switch and then you load the data. Then at
that moment you are almost up to date with your metadata.

Cheers again,

Ignacio


Sherlock, Kevin [TeamSybase] Posted on 2009-06-22 19:02:20.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a3f9be6@forums-3-dub.sybase.com> <4a3f9efc.2c3.1681692777@sybase.com>
Subject: Re: Staging area in ASE
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3fd53c@forums-3-dub.sybase.com>
Date: 22 Jun 2009 12:02:20 -0700
X-Trace: forums-3-dub.sybase.com 1245697340 10.22.241.152 (22 Jun 2009 12:02:20 -0700)
X-Original-Trace: 22 Jun 2009 12:02:20 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27901
Article PK: 77149

sure. whatever adjustments need to be made to fit your situation. Note
also that I mention that the ODSfeeder application should loop through both
sets of tables each time it's fired _then_ sleep. That way, if there is
data in either of the staging tables, it gets loaded.

<Ignacio Vera> wrote in message news:4a3f9efc.2c3.1681692777@sybase.com...
> One more thing:
>
> In your approach for the ODSFeeder:
>
> select @odsfeed_table = odsfeed_table from ods_ctl where
> ods_table = 'employee'
> <load ODS table "employee" from _a or _b stagefeed table
> based on @odsfeed_table setting>
> <truncate table _a or _b based on @odsfeed_table setting>
> update ods_ctl -- switch tables set stagefeed_table =
> odsfeed_table , odsfeed_table = stagefeed_table where
> ods_table = ' employee'
>
> If I understand correctly you load the data in the loading
> table before doing the switch. This means if the switch is
> done once a day, you will be inserting data from the day
> before, in other words, there is one day of delay in the
> data in the ODS.
>
> Would it be possible to do the ODSFeeder like:
>
> update ods_ctl -- switch tables set stagefeed_table =
> odsfeed_table , odsfeed_table = stagefeed_table where
> ods_table = ' employee'
> select @odsfeed_table = odsfeed_table from ods_ctl where
> ods_table = 'employee'
> <load ODS table "employee" from _a or _b stagefeed table
> based on @odsfeed_table setting>
> <truncate table _a or _b based on @odsfeed_table setting>
>
> You first do the switch and then you load the data. Then at
> that moment you are almost up to date with your metadata.
>
> Cheers again,
>
> Ignacio


Sherlock, Kevin [TeamSybase] Posted on 2009-06-22 19:09:16.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a3f9be6@forums-3-dub.sybase.com> <4a3f9efc.2c3.1681692777@sybase.com>
Subject: Re: Staging area in ASE
Lines: 44
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a3fd6dc@forums-3-dub.sybase.com>
Date: 22 Jun 2009 12:09:16 -0700
X-Trace: forums-3-dub.sybase.com 1245697756 10.22.241.152 (22 Jun 2009 12:09:16 -0700)
X-Original-Trace: 22 Jun 2009 12:09:16 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27902
Article PK: 77150

I've re-read you post, and no, I would disagree with your adjustment. Just
loop through the logic I posted twice before sleeping the ODSfeed
application. That way you will check both sets of tables for each iteration
of the ODSFeeding application.

<Ignacio Vera> wrote in message news:4a3f9efc.2c3.1681692777@sybase.com...
> One more thing:
>
> In your approach for the ODSFeeder:
>
> select @odsfeed_table = odsfeed_table from ods_ctl where
> ods_table = 'employee'
> <load ODS table "employee" from _a or _b stagefeed table
> based on @odsfeed_table setting>
> <truncate table _a or _b based on @odsfeed_table setting>
> update ods_ctl -- switch tables set stagefeed_table =
> odsfeed_table , odsfeed_table = stagefeed_table where
> ods_table = ' employee'
>
> If I understand correctly you load the data in the loading
> table before doing the switch. This means if the switch is
> done once a day, you will be inserting data from the day
> before, in other words, there is one day of delay in the
> data in the ODS.
>
> Would it be possible to do the ODSFeeder like:
>
> update ods_ctl -- switch tables set stagefeed_table =
> odsfeed_table , odsfeed_table = stagefeed_table where
> ods_table = ' employee'
> select @odsfeed_table = odsfeed_table from ods_ctl where
> ods_table = 'employee'
> <load ODS table "employee" from _a or _b stagefeed table
> based on @odsfeed_table setting>
> <truncate table _a or _b based on @odsfeed_table setting>
>
> You first do the switch and then you load the data. Then at
> that moment you are almost up to date with your metadata.
>
> Cheers again,
>
> Ignacio