Weekly Tips from DB2 Experts: Explore db2cli - DB2 interactive CLI command

Body

Hello,

To troubleshoot an issue with complex CLI application, DB2 CLI script with similar functionality can be used.

In many cases, customers usually have a complex CLI application that run into issues.
To simplify the issue and to quickly gather diagnostics, we often ask customers to try with a simple DB2 CLI script.

Here are some Windows based examples:

1. To test the CLI connectivity to the remote database from the DB2 Client where the application runs:                   
                                                                       
opt calldiag on                                                        
sqlallocenv 1                                                          
sqlsetenvattr 1 SQL_ATTR_ODBC_VERSION 2                                
sqlallocconnect 1 1                                                    
sqlsetconnectattrw 1 SQL_ATTR_ANSI_APP 0                               
sqldriverconnectw 1 0 "DSN=<DSNNAME>;UID=<UID>;pwd=<PWD>;" -3 512 SQL_DRIVER_NOPROMPT 
sqltransact 1 1 SQL_COMMIT
killenv 1

2. To check the performance of a query:

opt calldiag on
quickc 1 1 <dsnname>  <uid> <pwd>
sqlallocstmt 1 1
sqlprepare 1 "<SQL statement> " -3
system echo %TIME%
sqlexecute 1
system echo %TIME%
fetchall 1
system echo %TIME%
sqlfreestmt 1 SQL_CLOSE
sqltransact 1 1 SQL_COMMIT
killenv 1

To run the scripts:

1) Replace the dsnname, uid, pwd with yours and save it as <filename>.
2) From DB2 Command Window (db2cmd) Run:  db2cli < <path>/<filename>

If the issue is reproducible using the above script, then collect data as explained here.
Thanks!

Refer: db2cli