Technical Blog Post
Abstract
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
UID
ibm11141330