Question about how Texis handles CREATE TABLE AS statements in Vortex script

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Question about how Texis handles CREATE TABLE AS statements in Vortex script

Post by barry.marcus »

Our process has code that builds a set tables containing a subset of the data in some of our tables. The code basically loops over a list of existing table names and executes a series of statements that look like this:

create table [newTableName] as select * from [existingTableName] where [someCriteria]

Immediately after the loop completes (that is, at the time that the very next line of code after the loop is encountered) it seems as though the data in the *source* tables ("existingTableName" in the above example) is still locked.

(To be honest, I don't know if the tables are locked. All I know is that the very next statements are sql selects against the source data and they don't return anything as long as Texis is dealing with the data in
newly created tables.)

If that is the case -- that is, if locking is the issue -- my question is how can I tell when the create table process is done? Put another way, how do I know when I can succesfully retrieve the data in the source tables? Is there a way to determine when locks are released (if locking is the issue)? Can I issue some kind of sql statement against the newly created tables that requires those tables to be fully built, thereby forcing the create table process to complete and (hopefully) letting me get to the data in the source tables.

Thanks
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

Question about how Texis handles CREATE TABLE AS statements in Vortex script

Post by Kai »

By "don't return anything", you mean those very-next-statements finish (do not hang), but they return 0 rows, correct?

A table should never be locked once the <sql> statement returns control to Vortex, either to the statement(s) inside the <sql> loop or after it. (And the erroneous presence of a lock would likely manifest as a hanging statement, not 0 rows returned.)

What happens if you do a <sqlcp cache close> after the CREATE TABLE but before the very-next statements?

And the perennial question: Can you produce a small, self-contained example of the issue?
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Question about how Texis handles CREATE TABLE AS statements in Vortex script

Post by barry.marcus »

You're gonna make do that, aren't you, Kai?!

OK...

:)
Post Reply