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

[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

[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]

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

OK...