We have different users on our teradata database and a user which can be accessed by all other users.
To illustrate my issue we have
user ab ( accessed by a and b, we dont log in with the user ).
a is creating a macro for user ab, that b can access the macro.
This macro creates a volatile table called 'tab' and is called 'create_volatile'.
b can now execute the macro by 'EXEC ab.create_volatile;' and use the volatile table 'tab' in his current session as he likes.
a creates another macro 'select_volatile' for user ab which selects from the 'tab' table for user ab.
a can use the the macro by 'EXEC ab.select_volatile;' in his session after executing the 'create_volatile' macro first.
When b uses the 'select_volatile' macro in his session the macro fails because of the error:
STATEMENT 9: EXEC failed. Failed [3807 : 42S02] Object 'a.tab' does not exist.
So the problem is the select macro tries to find the volatile table in the spoolspace from the creater of the macro, in this case user a, that does not exist and it fails.
If the macro creates a volatile table instead of the select it does work so I imagine this is not the desired behaviour of the code?
Any help is appreciated, my dirty solution would be to put everything I do in macros into volatile tables and don't use selects. This would be sad.
As you've spotted this is not going to work.
What's happening is that when you create/replace the 'select_volatile' macro all objects are fully qualified, so (as you've worked out) the volatile table name is fully qualified to the name of the creating user.
I think you'll have to use a Stored Procedure (SP) to achieve this.
From a business/operational perspective, what are you trying to achieve?
Of course, a Volatile Table is always session local and the databasename is always the user who created that tabble.
There's no way to access a Volatile Table created in a different session.
thank you for your explanation, what makes me wonder is if the 'select_volatile' macro would be a 'create_volatile_2' macro which uses the volatiles created in 'create_volatile' the macros do work.
We want to create macros for our hole team for different purposes. At the moment everybody has different macros and we want to standardize all our script.
Our team does not have access to write stored procedures at the moment..
that's not the question, the volatiles are bound to a session ofc - but the macro tries to use a different user as the executor which is the problem.
It doesn't matter what th emacro is called, you can't do what you're trying to do using VT's and macros - at least not this way.
I understand about trying to get everyone to use standard macros, that makes perfect sense.
How about the following?
- Each user (user A and user B in your example) has the 'create_volatile' macro created under their own userid.
- The source for the 'select_volatile' macro is stored outside of Teradata, say on a shared drive where everyone can get to it.
- When a user wants to 'do whateever', they "execute create_volatile" macro under their own userid
- create/replace the 'select_volatile' macro under their own userid
- "execute select_volatile" under their own userid.
This should technically work although (to be honest) unless the 'select_volatile' macro has parameters to it which the user might want to change on multiple invocations then I'd suggest just running a select against the VT created by 'create_volatile'.
Multiple users using the same temporary table is a the main usage of a Global Temporary Table.
You create it once in user ab, grant Select & Insert rights and the macro simply Insert/Selects into the GTT instead of creating a VT.
Now you can have macros/views accessing that GTT and each session got it's own instance with different data.
Thank you both for your suggestions.
Every macro loaded per user was the first idea, but to keep everyone up to date with their macros would be to much organisational effort which should have been avoided using a common user.
What I will do is look into Global Temporary Table's and use this case to start the evaluating process for stored proccedures, which would save us a lot of workarounds in the macro architecture anyway.