Create,drop macros using JDBC

Tools

Create,drop macros using JDBC

Hi,

I need to create 1000 of macros. I am using JDBC for the same.
But i find that since we need to issue a commit after each create macro statement. if the commit is not issued, the subsequent create macro execution
fails saying that "Teradata: et or commit was expected"
I believe this is expensive and quite slow.
Please let me knoe if there is a faster alternative to this?

Attached is the sample code which is used to create a macro using JDBC. These statements are called in a loop.

statement = dbConnection.createStatement();
rowCount = statement.executeUpdate(insertMacro);
statement.close();
dbConnection.commit();
statement = dbConnection.createStatement();
rowCount = statement.executeUpdate(updateMacro);
statement.close();

Rgds,
Ranga
1 REPLY
Enthusiast

Re: Create,drop macros using JDBC

The basic answer is no - you cannot speed it up.
Setting up the macros is expensive because of the dictionary updating - you can get some very slow responses when you set up lots of database objects like this.

Keeping you AccessRights table super-clean can help speed up creating database objects, but it is a real pain to do.

Take a look at why you need thousands of macros? If the macros are similar but have a different "Where" clause, can you parameterize the macro? If the macros are for different (bit look-alike) tables, can you use a Stored Procedure with dynamic SQL?

Can you send an example of the generated SQL from a couple of your macros?