as400 acsbundle.jar IBM

I'm working on AS400. I'm completely new on this tool.

When I launch the IBM tool interface I can click on a link to launch an other window that allows me to execute SQL scripts.

Then I can to execute 1 command and then a SQL request.

The first command (according to what I understood) allows me to filter the data while the second command is a basic SQL request.

I want to be able to execute this 2 commands but in command line with the jar directly.

I tried to do as below :

java -jar /Applications/IBMiAccess/acsbundle.jar /plugin=rmtcmd /cmd="call myLib/myProg '20200706 20200708 10047'" /system=my.ip.address

java -jar /Applications/IBMiAccess/acsbundle.jar /plugin=cldownload /system=my.ip.address /clientfile=/Users/MYUSERNAME/Downloads/test.xlsx /sql="SELECT * FROM MYDB.MYTABLE" /userid=MYUSERID

The first command says : the program has been executed correctly but when I try to download the result (with the second command) it's not the one expected... Worse I can even execute the program without any param and it says the program is executed normally.
When I open my file it's only give me the data from the current day :/

So if anyone can help me with this that'll be greatly appreciated ! Thanks a lot

It would be useful to understand what is happening here - I assume the program writes your filtered output to a table, which the SQL statement then reads?
I would guess your problem with automation is that the second request is starting before the first one finishes, because they will be running in completely separate processes.
You would probably be better off writing (or asking someone else to write) a stored procedure to call the program in the first step from SQL, so that you could call the whole lot in one go as a SQL script.

I found 2 solutions :

The first one is using the plugin rss.. If nobody can help you understand what is the previous lib / prog exactly do then you can something like :

  for indexArray in "${!ips[@]}"
  do
    nameFile="${workingFolder}""${warehouse[$indexArray]}".csv
    java -jar $pathIBM/acsbundle.jar /system="${ips[$indexArray]}" /plugin=logon /USERID=${USERNAME} /PASSWORD=${PASSWORD}
    java -jar $pathIBM/acsbundle.jar /system=${ips[$indexArray]} /plugin=rss /sql="CALL MY_LIB.MY_PROG(${date28DaysAgos}, ${todayDate},${store})" /autorun=1 &
    pid=$!
    sleep 30
    kill -9 $pid
    java -jar $pathIBM/acsbundle.jar /plugin=cldownload /system="${ips[$indexArray]}" /clientfile="${nameFile}" /sql="SELECT * FROM MYDB.MYTABLE" /userid=${USERNAME}
  done

Basically, this solutions allows you to use the rss plugin. Depending of the time of execution of your lib (CALL MYPROG.MYLIB you should increase the sleep time before killing the process.

Next solution, cleaner of course, will be to have a nice SQL request where you know what you need and then :

for indexArray in "${!ips[@]}"
do
    nameFile="${workingFolder}""${warehouse[$indexArray]}".csv
    java -jar $pathIBM/acsbundle.jar /system="${ips[$indexArray]}" /plugin=logon /USERID=${USERNAME} /PASSWORD=${PASSWORD}
    java -jar $pathIBM/acsbundle.jar /plugin=cldownload /system="${ips[$indexArray]}" /clientfile="${nameFile}" /sql="${sql}" /userid=${USERNAME}
done