Same piece of SQL against multiple databases.

[Kaos]

I work in an operations group - part of the job function is that we have to run any ad-hoc SQL that needs to be represented as a change on a change control call.

Lately we've been getting requests to run the same SQL in anywhere from 2-60 different oracle databases. (for the case of an example - running an update statement to change some values in a user table).

For some reason I guess this problem hasn't come up before.

I'm trying to intelligently solve it so I'm not wasting time in the future logging into each one and running it manually.

Work I've done so far:

gathered all user names, passwords and what databases they are on one into a mysql database running on my machine (where I'm testing for the moment).

I have a column for each of those values and I have them all populated.

Conceptually - how do you think the best way to handle this would be?

I was thinking of just creating a PHP page with a bunch of checkboxes for the various databases and then a box to put the SQL into and it would chug along and maybe keep a little log of what it did along the way.

At the same time I'm kind of interested in learning python and maybe this would be a great first project.

Who knows, maybe there are tools out there that handle this problem already (MuSQL was one I saw but it doesnt seem to like that I didn't have a common username/password across all the databases).

 

[PTNL]

So the workflow you're wanting to do is the following:
- Determine mapping logic between Oracle database schemas and MySQL database schemas, also handling any data type conversions as needed.
- Regularly dump data from multiple Oracle databases to MySQL.
- Create a GUI interface to construct dynamic queries against the MySQL database.

There's a lot of potential issues with this approach, but I'm not sure which ones would affect you. Some off-hand questions:
1 - How many tables and records are we talking about on Oracle?
2 - What does a simple query and a complex query look like? We'll need to see the exact statement, but adjust column/table names if necessary to obscure any personal or identifying information.
3 - How often are you asked to do these statements?
4 - And arguably the most important question: Why does the proposed solution sound more appropriate and safer versus writing a GUI frontend (or a couple GUI frontends) for directly updating the Oracle databases?

 

[Kaos]

No I just want to do the following

1. User submits SQL and a list of databases it needs run against - they do not have logins for these. My group is the keeper of that data.
2. I want to create a tool that can log in to those databases one after another and run the SQL, rather than manually doing it one after another in PL/SQL developer.
3. I have a list of the login details stored in a mysql database. - I dont need to move data between oracle/mysql at all.

 

[Pwyl_The_Destroyer]

No I just want to do the following

1. User submits SQL and a list of databases it needs run against - they do not have logins for these. My group is the keeper of that data.
2. I want to create a tool that can log in to those databases one after another and run the SQL, rather than manually doing it one after another in PL/SQL developer.
3. I have a list of the login details stored in a mysql database. - I dont need to move data between oracle/mysql at all.

Who is the user in #1

 

[Monkey God]

How dis-similar is this data?
If its very dis-similar, how can you expect similar queries to work?
If its not dis-similar, then: Data warehouse and normalization says wut?

 

[PTNL]

No I just want to do the following

1. User submits SQL and a list of databases it needs run against - they do not have logins for these. My group is the keeper of that data.
2. I want to create a tool that can log in to those databases one after another and run the SQL, rather than manually doing it one after another in PL/SQL developer.
3. I have a list of the login details stored in a mysql database. - I dont need to move data between oracle/mysql at all.

This sounds like an approval workflow for database schema changes. Is that assertion correct?

Who is the user in #1

Agreed. Knowing this helps understand the context of the implementation.

 

[Kaos]

Users are people working in various groups in our company. They arent allowed access to production systems.

Lets say they need a field for their user account flipped from "N" to "Y"

They (or sometimes they just know they need the option enabled and I or one of my coworkers will write the SQL) will say "hey I need my "is_user_cool" value adjusted from N to Y for all the databases I have a login on (which are basically separate customer databases that all follow the same schema/configuration)

so we'll use or write something like this:

update user_table set is_user_cool = 'Y' where username in ('user1', 'user2', 'user3');

But since the customers databases (which we host here) have to be separate, we end up having to run stuff like this in anywhere from 2-60 different schemas with the accounts we are 'allowed' to use (which are the schema owners). Sucks up a lot of time, IMO.

I wish I had one login on each oracle instance and then I could just use the alter session commands to do it, but I dont and the DBAs wont grant us those types of logins either,

 

[PTNL]

Users are people working in various groups in our company. They arent allowed access to production systems.

Lets say they need a field for their user account flipped from "N" to "Y"

They (or sometimes they just know they need the option enabled and I or one of my coworkers will write the SQL) will say "hey I need my "is_user_cool" value adjusted from N to Y for all the databases I have a login on (which are basically separate customer databases that all follow the same schema/configuration)

so we'll use or write something like this:

update user_table set is_user_cool = 'Y' where username in ('user1', 'user2', 'user3');

But since the customers databases (which we host here) have to be separate, we end up having to run stuff like this in anywhere from 2-60 different schemas with the accounts we are 'allowed' to use (which are the schema owners). Sucks up a lot of time, IMO.

Ah, so this is different than my initial understanding. You're trying to create a "... for dummies" version of TOAD. That, in itself, is a huge endeavor.

And after reading the context of the users that would utilize the proposed system, I see so many concerns and disastrous opportunities for bulk failure. Here are just a few of my concerns:
1 - The users you've described may not know the database schema, how data translates across normalization, etc.
2 - Somewhat related to #1.... Your simple example and SQL statement does nothing to convey scope or context of the column or how such a change could cascade, depending on business rules or how other reports/queries are factored.
3 - While you state that the users "arent allowed access to production systems", this is basically giving them a backdoor GUI that bypasses any business rules or constraints. How would you stop one user from updating user accounts that, from a business rules perspective, are only to be maintained by another user?


The real solution is to create a GUI, but one that does not allow the users to build their own SQL logic. You'll want one that can only return data by certain criteria, programmatically enforces business rules/filters, offers useful error messages, and better logging for CYA scenarios than database transaction logs (which is not a simple process to parse).

Start by identifying the query-related requests from users, in order from most common to least common. If this is a case where users are screwing up way too often and relying on you to fix the screwups, then let that roll into a user-process change - perhaps even a change to the existing system. If there's a legitimate business need for some queries, find out how to integrate a few of the painful or constant SQL requests into the existing workflow and/or applications.

 

[Kaos]

The people submitting the SQL would not be using it in any way - only my group has the access. This is just an attempt to ease us from running the same SQL statement in multiple databases by hand.

The SQL we run is controlled by the developers - the users are basically giving us the values for whatever variables need to be set up (like usernames). I was just trying to simplify the explanation.

 

[PTNL]

The people submitting the SQL would not be using it in any way - only my group has the access. This is just an attempt to ease us from running the same SQL statement in multiple databases by hand.

The SQL we run is controlled by the developers - the users are basically giving us the values for whatever variables need to be set up (like usernames). I was just trying to simplify the explanation.

So if you (or the developers) know all of the moving and affected parts, then is there a need to keep yourself part of the workflow of a business user changing a value within a record? Such a change would be based on a business need, so do you still need (or want) to have the change request submitted to you or can the users make the change immediately through the GUI you're thinking of?

 

[Kaos]

So if you (or the developers) know all of the moving and affected parts, then is there a need to keep yourself part of the workflow of a business user changing a value within a record? Such a change would be based on a business need, so do you still need (or want) to have the change request submitted to you or can the users make the change immediately through the GUI you're thinking of?

It's just corporate change control policies.

Developers arent allowed access to make production changes.
Internal users can't either.
Operations can.

Internal users who need something done get with a developer who will either use an existing script/stored proc to do it or will write one. Then they submit a ticket to Operations and we perform the work in production.

 

[Snowpea]

It sounds like he just needs an app that can query multiple DBs with whatever he wants, provided the query is written so that it is standardized across these multiple DBs. In that case, since you have all these logins etc, you can write a small script that creates a handle to each DB and executes your query across all selected DBs. This would really just be like 40 lines of code in PHP, and of course you'd want to figure out a secure way to store all your DB login info so you can access it and loop through it while created DB handles.