Asking about query's restriction (over 32000 byte) about "wlmhist.pl" that is sample perl script where is located in $HOME/sqllib/samples/perl.

[sjcsh]

Hello, team.
i'm SeokHwan choi from db2 support team. i'm posting to ask "wlmhist.pl" which is sample perl script where is located in $HOME/sqllib/samples/perl.

[Question]
Q1) Could you please make sure if the "STMT_TEXT" column of "ACTIVITYSTMT_DB2ACTIVITIES" can store long-query that is over 32000 characters?

Q2) How to do extract long-query that is over 32000 characters from "STMT_TEXT" column of "ACTIVITYSTMT_DB2ACTIVITIES" ?

Q3) How to do insert long-query that is over 32000 characters to WLMHIST table ?

Q4) we tried to reproduce a similar issue via sample Perl. L3 team got a message like Error: Data in column 0 has been truncated to 32700 bytes". So, could you please make sure if "Error: Data in column 0 has been truncated to 32700 bytes" is related to DB29320W?
"DB29320W Output has been truncated."

Q5) Is there any restriction that is related to query length from the perl? e.g, it can't retrieve over 32000?

Q6) if customer has a problem in terms of sample code where located in $HOME/sqllib/samples/, should customer ask via this github page? or is it correct way to open DB2 SF case?

Note that, I've been handling DB2 case "TS009058089" about this case.

Background history
1)Customer has been using wlmhist.pl" that is sample perl script where is located in $HOME/sqllib/samples/perl. when customer tried to retrived full sqls that worked for business purposes. it has been truncated.
in short, customer wasn't able to get full sql. ( from this script, there is variable "$sqlToGetStmt" to extract various informations including "stmt_text" via activity event monitor.)

"select **a2.stmt_text,** a1.time_created, a1.time_started, ".
"a1.time_completed, a1.activity_id, a1.uow_id, a1.appl_id, ".
"a1.activity_secondary_id, ".
"a1.activity_type, a1.session_auth_id, a2.comp_env_desc ".
"from $activity as a1, $activityStmt as a2 ".
"where a1.activity_id = a2.activity_id and a1.uow_id = a2.uow_id and ".
"a1.appl_id = a2.appl_id and ".
"a1.activity_secondary_id = a2.activity_secondary_id and ".
"$checkPartitionText".
"partial_record = 0 and ".
"appl_name != 'DB2HMON' and ".
"(a1.activity_type = 'DML' or a1.activity_type = 'READ_DML' or ".
"a1.activity_type = 'WRITE_DML') ".
"$fromTimeText $toTimeText $workloadIdText $serviceClassText ".
"$serviceSubClassText";

2)"STMT_TEXT" has been setup as CLOB, and db2 moniotor L3 team said STMT_TEXT can store statements bigger than 32000 characters. The event monitor can accommodate statements up to 2Gb in size. however, it has truncated full SQL when perl has handled SQL.
when L3 team tried to reproduce via simple way, it has returned message like ""Error: Data in column 0 has been truncated to 32700 bytes" for SQL that length was 8166.

db2 "select length(stmt_text) from ACTIVITYSTMT_ACT"

1
-----------
        268
       8873
        584
      81661

  4 record(s) selected.

$ db2 describe table ACTIVITYSTMT_DB2ACTIVITIES

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PARTITION_KEY                   SYSIBM    INTEGER                      4     0 No
ACTIVATE_TIMESTAMP              SYSIBM    TIMESTAMP                   10     6 No
ACTIVITY_ID                     SYSIBM    BIGINT                       8     0 No
ACTIVITY_SECONDARY_ID           SYSIBM    SMALLINT                     2     0 No
APPL_ID                         SYSIBM    VARCHAR                     64     0 No
COMP_ENV_DESC                   SYSIBM    BLOB                     10240     0 No
CREATOR                         SYSIBM    VARCHAR                    128     0 No
EFF_STMT_TEXT                   SYSIBM    CLOB                   2097152     0 Yes
EXECUTABLE_ID                   SYSIBM    VARCHAR                     32     0 No
NUM_ROUTINES                    SYSIBM    INTEGER                      4     0 No
PACKAGE_NAME                    SYSIBM    VARCHAR                    128     0 No
PACKAGE_VERSION_ID              SYSIBM    VARCHAR                     64     0 No
PARTITION_NUMBER                SYSIBM    SMALLINT                     2     0 No
PLANID                          SYSIBM    BIGINT                       8     0 No
ROUTINE_ID                      SYSIBM    BIGINT                       8     0 No
SECTION_ENV                     SYSIBM    BLOB                 140509184     0 No
SECTION_NUMBER                  SYSIBM    BIGINT                       8     0 No
SEMANTIC_ENV_ID                 SYSIBM    BIGINT                       8     0 No
STMT_FIRST_USE_TIME             SYSIBM    TIMESTAMP                   10     6 No
STMT_INVOCATION_ID              SYSIBM    BIGINT                       8     0 No
STMT_ISOLATION                  SYSIBM    BIGINT                       8     0 No
STMT_LAST_USE_TIME              SYSIBM    TIMESTAMP                   10     6 No
STMT_LOCK_TIMEOUT               SYSIBM    INTEGER                      4     0 No
STMT_NEST_LEVEL                 SYSIBM    BIGINT                       8     0 No
STMT_PKGCACHE_ID                SYSIBM    BIGINT                       8     0 No
STMT_QUERY_ID                   SYSIBM    BIGINT                       8     0 No
STMT_SOURCE_ID                  SYSIBM    BIGINT                       8     0 No
STMT_TEXT                       SYSIBM    CLOB                   2097152     0 Yes
STMT_TYPE                       SYSIBM    BIGINT                       8     0 No
STMTID                          SYSIBM    BIGINT                       8     0 No
STMTNO                          SYSIBM    INTEGER                      4     0 No
UOW_ID                          SYSIBM    INTEGER                      4     0 No

3)Usually, customer has been using "wlmhist.pl" via the purpose below.

1.select long query that is over 32000 characters from activitystmt_db2activies
2.insert query that extracted in No,1 to WLHHIST table

in this case, customer said it looks like either 1) the long query that is over 32000 characters was truncated when it tried to select "STMT_TEXT column of ACTIVITYSTMT_DB2ACTIVITIES" or 2) there was a problem when it tried to insert WLMHIST table.2.insert query that extracted in No,1 to WLHHIST table

Currently, customer has added new line under "my $sqlToGetStmt" like 'activity.jpg'

[21:kr007491@ecurep]:/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety $ pwd
/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety
[21:kr007491@ecurep]:/ecurep/sf/TS009/058/TS009058089/mail20220421-084547-sety $ ls -al
total 20484
drwxrws--- 2 root swsupt 4096 Apr 21 08:46 .
drwx--S--- 19 root swsupt 4096 Apr 29 01:51 ..
-rw-r----- 1 root swsupt 6222 Apr 21 08:46 MAILINFO.wri
-rw-r----- 1 root swsupt 5136280 Apr 21 08:46 activity.jpg <<---
-rw-r----- 1 root swsupt 27246 Apr 21 08:46 mail.html
-rw-r----- 1 root swsupt 1152 Apr 21 08:46 mailimg.1.gif

4)I see that the perl code uses DBI for its SQL capability:

**use DBI;**

I cannot comment on how the perl script can result in DB29320W.

Best regards,

SH.