In Netezza, you can’t use the “USE” command to change the current default or working schema. You have to log into the working schema, and there you will stay till the end of the session. Don’t shoot the messenger, that is the way it is.
Here is the problem with our standardized queries: Some of them default to the working directory of the CDM tables (e.g. in the Cohort Method), others are defaulting to the schema where results are written to (e.g. the Treatment Pathways Study 1).
We can solve either problem, but not both:
If the script defaults to the CDM we can create a bunch of CREATE SYNONYM commands, because I know what tables there are (aka CDM).
If the script defaults to the results schema we can just log into it and omit the USE command altogether.
Here is the problem: At SQLTranslate time, I don’t know which one it is. Martijn suggested to check wether the “USE blablabla” command contains the string “CDM” and then write synonyms to it. That is very klugy.
Now my question: could we adopt as a community one of two solutions to this:
We always default to only one (either the CDM or writing schema, I don’t care which one)
We stop defaulting, and always use the full reference to a table (schema.tablename)
Let me know what you think. But we need to solve this, otherwise folks running Netezza boes are excluded from the network.
Given the likelihood that we’ll want analytics to run under credentials that don’t have write access to the CDM schema, I’d vote for the second option. I’m also not averse to SqlTranslate picking up markup to add a “default schema qualifier” and implementing “USE” there.
@Christian_Reich Have you tried the Netezza “set schema” command? Is it available with the version of NPS that you are running? If not, you could upgrade your Netezza to a later NPS.
Yeah. No schemas at AZ, remember? You guys made that decision!
There is a command SET CATALOG, which switches the user. No idea why they call it “catalog”, but that’s what it is. However, you need a new version of the software as well, and it stuck with something Mitesh explained to me. They are trying.
But at any rate. We might have that problem no matter what. We can solve it with conventions.
It’s been 3 years since AZ got the Netezza appliance - it’s probably time for an upgrade of NPS and the firmware
I would recommend that AZ (Mitesh?) contact IBM support and schedule an upgrade. Yes you are right, schema support would need to be enabled. That is just a Netezza configuration change but it would require some planning and testing of existing applications.
My company, LTS Computing LLC, can help with all of the above if AZ needs additional Netezza expertise.
You are right. And, they started doing it and got stuck with something Mitesh explained but I forgot. BTW: I told him to reach out to you. He is very polite and shy, so, try to be nice to him. :)
I was calling for a set of guidelines when we try to write parametized SQL and at that time it was dismissed that manual for SQLRender is sufficient. However, I was arguing that we need something like best practices for actual use of SQLRender and this is exactly that problem.
I kind of like the solution where I am in the CDM schema with data and if I don’t need any write access to any result tables, I just stay within that schema and run my queries (no prefixes needed). just plain person or measurement table.
If I happen to need write data into results tables, I create those and prefix those.
So I vote for option 1 proposed with default to the CDM schema with data I am working on.
I would prefer to make Netezza behave rather than changing everything because of that. (But I also have my favorite marginal db (RedShift).) So I want to be attentive to special requests.
Just chiming in here: I think it’s risky to assume the search schema is what you think it is. example, we have a cohort table in cdm schema and result schema. You want to be sure about which one you write to. I think be should always be prefixing tables with the schema, but I also realize that this would be a cumbersome shift for some of us to make.
