Hi,
I use "sqlcomp/in xxx/xxxo" command to see cost of sql sentences on Guardian, I mean estimate cost. I make sql compiler all of the object file into a text file and cobol programme read "estimate cost:" sentences and check values.

On OSS system, we use java programme language and create .jar files and bathc system run this .jar file.
Now I wanto to learn estimate cost of sql sentences in java code, is it possible.
Thanks.

On Wednesday, August 24, 2022 at 11:14:07 PM UTC-7, Memmedaga Memmedov wrote:
> Hi,
> I use "sqlcomp/in xxx/xxxo" command to see cost of sql sentences on Guardian, I mean estimate cost. I make sql compiler all of the object file into a text file and cobol programme read "estimate cost:" sentences and check values.
>
> On OSS system, we use java programme language and create .jar files and bathc system run this .jar file.
> Now I wanto to learn estimate cost of sql sentences in java code, is it possible.
> Thanks.

Since no one else has tried to answer yet, I will step in with what I think is the answer.

In short, if I understand your question, you cannot do that with Java programs.

For COBOL, C, or TAL program that use embedded SQL on the NonStop system, the language compiler takes the SQL statements, saves them in a special part of the object file, and replaces the SQL statements with calls to the SQL executor procedures that will carry out the actions the SQL statement requests. Later, when the SQL Compiler is used to compile the SQL statements that are in the object file, the SQL compiler gets those statements from that special part of the object file, compiles each statement, and places each compiled query plan in another special part of the object file. When you run the program, when execution reaches the spot where there was an embedded SQL statement, the SQL executor is called, it finds the compiled query plan for the statement that was originally at that spot in the program, and performs the steps of the query plan.

When you run SQLCOMP and ask it to do EXPLAIN PLAN from the object file, it locates the compiled query plans from the special part of the object file where they are stored, and displays them in human-readable form.

For your Java programs, if they are using the usual JDBC classes to perform the SQL statements, there is nothing in the Java compiler that recognizes embedded SQL statements -- they are just character strings that get passed to some methods of the JDBC classes. There is no special place in the compiled Java code that holds either the SQL statements nor the compiled query plans. So there is no way to have a program interpret the compiled query plans from a Java jar file (because there are no compiled query plans there).. SQL statements in a Java program are only compiled at execution time.

You are not completely unable to look at the query plans that your Java program uses. You can find the SQL statements in your Java program, get to a TACL prompt, run SQLCI, and use its EXPLAIN command to get SQLCI to display the query plan for each of the SQL statements you found in the Java program, one at a time. That is not as convenient as you can do with SQL statement in a COBOL program, but it is possible to do that.

You must take care if the table names are not fully qualified in the Java code to be sure the defaults in effect when you run the EXPLAIN commands match what would be in effect when you run the Java program, so the SQL compiler references the same tables.

If the Java program composes the SQL statements by combining several Strings to make up the full SQL statement, that would make it somewhat difficult for you to be sure what the statement that gets executed will be. I don't have any suggestion about how best to deal with that.

Thanks for your repy, I expected an answer like this. In this case, what I need to do is to write a cobol program that contains the same sql statement for every sql statement I use in the java program.
Then I find out which java program I use for the high costs I have detected as a result of sql compilation.

On Thursday, September 1, 2022 at 12:43:39 AM UTC-7, Memmedaga Memmedov wrote:
> Thanks for your repy, I expected an answer like this. In this case, what I need to do is to write a cobol program that contains the same sql statement for every sql statement I use in the java program.
> Then I find out which java program I use for the high costs I have detected as a result of sql compilation.

You don't have to write that COBOL program. You can add the word "EXPLAIN" in front of a SQL statement in SQLCI and get what I believe is the same information. Just make sure all the table names in the SQL statement are fully qualified or that your defaults are set to the same as will be used when the statement is executed from the Java program.