Using saved SQL files in DB2 for i

[Gregory1701]

In our distributed system, we have our home office IBM i, and then around 80 other IBM i's. I have various SQL files saved locally on my laptop. For organizational purposes, I keep them all in a folder called 'SQL Files' and each SQL file is named for the table(s) or subject matter of the SQL statements within each file.

In RSS within ACS, this is quite simple to be connected to one IBM i, open a SQL file, do some investigation, then leave that file open and connect to a different IBM i and run those statements again.

My first hurdle with adopting DB2 for i was that it wasn't easy to open a local file. I then, on my 'home office' IBM i, created a subdirectory within my default IFS directory called 'SQL Files' and copied all of my SQL files from my local folder to my new IFS subdirectory. Then in the IBM i extension, I created an IFS shortcut to my new 'SQL Files' subdirectory. This works great! When I want to open one, I can just go to my IFS Browser section within the IBM i extension, double click the one I want to open and it opens in a DB2 for i window and I'm 'off to the races'.

Now then, comes the trick. When I want to switch to one of my other 80 IBM i's, while I can setup (albeit a bit time consuming) a connection for each of those systems, it's not feasible to maintain those SQL files on all 80 systems. Any ideas how to solve this dilemma?

[sebjulliand]

Here is something I've been trying lately to keep my SQL files at hand in VS Code regardless of which LPAR I'm connected to.
I used GitHub Gists from within VS Code using an extension called GistPad.

In the GistPad view, you get a nice explorer to browse your Gist and organize them. You can even browse your starred Gist if you favorited Gists from other users.

Give it a try, it may fit your need 😊

[Gregory1701]

Interesting! I'll give it a try. Thanks @sebjulliand!

[Gregory1701]

@sebjulliand - After reviewing GistPad, while a cool extension, we felt it was not appropriate to store all of my saved SQL files there. Primarily because that would be putting detailed information about our db in a public space.

[rogersonra]

@Gregory1701 , could you use three part naming?
SELECT *
FROM remote.schema.table;

This way your statement stays virtually the same and all you have to change is the remote name.

[Gregory1701]

@rogersonra - An interesting thought! It's not ideal for me, for example, the beauty of saving my SQL files is that some of them are fairly complex Queries joining many files together, but I was willing to give it a try.

I don't think (at it's current build at least) we can do three part naming. The schema browser only knows about schemas (libraries) on the current connection. And as I'm sure you're aware, in VS Code, you can only be connected to one system at a time. So, when I do try the three part naming and run the statement, I get this error:
[SQL0950] Relational database SYSTEM004 not in relational database directory., 42705, -950

Maybe I'm doing something wrong, but I think due to the single connection limitation, the three part naming is not a viable solution.