Basic Script SQL extraction + FTP Upload

[Gustavo Bertazzoli]

Hello Tandem Guys,

I'm quite good in the SQL/MP stuff from the Tandem. But now I need something a bit more complex, but unfortunately, I have completely zero knowledge on how to do it. Basically, what I have to do is to create a script which does the following steps:
- execute a SQL query and generate a txt file (easy part for me)
- connect to a FTP server(on the same network but out of tandem) via SFTP and upload the file there

My difficulties are:
- How to create the script
- How to execute the SQL inside of it
- How to ftp inside it

I know that Tandem have a completely different architecture and this script is probably quite different from bash scripts from Linux and so on.
But I have to start somehow, so, does anyone here have any documents to share to help me in order to start with it?

Thanks a lot in advance and greetings!
Gustavo Bertazzoli

[[e-mail]]

That is a very broad question. The best I can suggest are some pointers to pursue.

The native NonStop Guardian scripting language is called TACL. If you are completely new to NonStop, I think you would find the learning curve to be steep. There is a Posix personality within NonStop called OSS. In that environment, you might find tools that are more familiar to you. You will find things such as Bash, shell, vi, vim, and Python.

Your best option might be to do your extracts directly from Windows or Linux. If you can get the system owner to install the ODBC service, you can do your queries remotely and transfer the data extracts from there.

If you want to start browsing the HPE NonStop documentation, you can find it at: https://support.hpe.com/

Jon Marcus

[Keith Dick]

Post by j-***@pacbell.net
That is a very broad question. The best I can suggest are some pointers to pursue.
The native NonStop Guardian scripting language is called TACL. If you are completely new to NonStop, I think you would find the learning curve to be steep. There is a Posix personality within NonStop called OSS. In that environment, you might find tools that are more familiar to you. You will find things such as Bash, shell, vi, vim, and Python.
Your best option might be to do your extracts directly from Windows or Linux. If you can get the system owner to install the ODBC service, you can do your queries remotely and transfer the data extracts from there.
If you want to start browsing the HPE NonStop documentation, you can find it at: https://support.hpe.com/
Jon Marcus

I did not notice this question back when it was first posted or I would have replied sooner.

What Jon suggests is not bad, but might be more complex than what is needed. He is right in observing that the question was not very specific.

If we make the assumption that you do not need to pass parameters that vary each time you run the script to either the SQL or SFTP part, you can do things very easily, something like this:

Put the SQL/MP commands you want to run in an Edit file named SQLCMDS.
Put the SFTP commands you want to run in an Edit file named FTPCMDS
Create an Edit file, call it RUNIT for this example, that looks like this:

SQLCI/IN SQLCMDS/
SFTP -b FTPCMDS ***@ip-address

Then, at a TACL prompt, you would run the commands via the command:

OBEY RUNIT

I have not used SFTP on the NonStop system, but I know it is there (part of the SSH2 product). The commands SFTP accepts are documented in the NonStop SSH manual. They are mostly the familiar FTP commands, plus some additions. I believe the system manager needs to do some general configuration before SFTP is usable by non-privileged users, and I believe there is some central key/password component of SSH2 in which passwords must be set up. I don't know the details of that password setup, so you'd have to ask someone else about the details of that.

If parts of the SQL command or SFTP commands need to be specified for each separate time you run the script, there is a fairly easy way to do that, which does not require learning very much TACL. I won't complicate this answer to try to describe that, but if you do need to be able to specify parameters at the time you run the script, describe in a little detail what you need to specify, and we probably can show you a simple way to do that with TACL that won't require that you learn very much about TACL.

[Gustavo Bertazzoli]

Post by Keith Dick

Post by j-***@pacbell.net
That is a very broad question. The best I can suggest are some pointers to pursue.
The native NonStop Guardian scripting language is called TACL. If you are completely new to NonStop, I think you would find the learning curve to be steep. There is a Posix personality within NonStop called OSS. In that environment, you might find tools that are more familiar to you. You will find things such as Bash, shell, vi, vim, and Python.
Your best option might be to do your extracts directly from Windows or Linux. If you can get the system owner to install the ODBC service, you can do your queries remotely and transfer the data extracts from there.
If you want to start browsing the HPE NonStop documentation, you can find it at: https://support.hpe.com/
Jon Marcus

I did not notice this question back when it was first posted or I would have replied sooner.
What Jon suggests is not bad, but might be more complex than what is needed. He is right in observing that the question was not very specific.
Put the SQL/MP commands you want to run in an Edit file named SQLCMDS.
Put the SFTP commands you want to run in an Edit file named FTPCMDS
SQLCI/IN SQLCMDS/
OBEY RUNIT
I have not used SFTP on the NonStop system, but I know it is there (part of the SSH2 product). The commands SFTP accepts are documented in the NonStop SSH manual. They are mostly the familiar FTP commands, plus some additions. I believe the system manager needs to do some general configuration before SFTP is usable by non-privileged users, and I believe there is some central key/password component of SSH2 in which passwords must be set up. I don't know the details of that password setup, so you'd have to ask someone else about the details of that.
If parts of the SQL command or SFTP commands need to be specified for each separate time you run the script, there is a fairly easy way to do that, which does not require learning very much TACL. I won't complicate this answer to try to describe that, but if you do need to be able to specify parameters at the time you run the script, describe in a little detail what you need to specify, and we probably can show you a simple way to do that with TACL that won't require that you learn very much about TACL.

Hey guys,

Thanks a lot.
First of all, yes, I'm quite beginner in NonStop in terms of TACL and TACL Macros and so on. I'm working with the same system since 2015 which runs on NonStop, but I never had to do something similar like that.

@Keith, what you showed is exactly what I need. I will play around now with that. Thanks a lot once again.