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.
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?