News
Abstract
This document provides details on viewing CLOB, BLOB, or DBCLOB fields in ISQL (STRSQL) from IBM i.
Content
The CL command STRSQL is provided with product 5770ST1. This provides an interface to run SQL interactively. IBM does suggest clients use Access Client Solutions and Run SQL Script to run SQL interactively as ACS is the strategic product and 5770ST1 is no longer being enhanced.
Many SQL tables have CLOB, BLOB, or DBCLOB fields. The STRSQL interface does not support these field types and you see *POINTER displayed instead of the actual data. To view this data you need to cast the field to VARCHAR. VARCHAR is limited to a length of 32,740. You may need to subset the data to more than one field if your data is larger than 32,2740 characters.
Example without casting to VARCHAR.
SELECT *
FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/DIR/file1.txt'))
Results:
LINE_00001 LINE
1 *POINTER
Example with casting to VARHCAR.
SELECT cast(line as char(10))
FROM TABLE(QSYS2.IFS_READ_BINARY(PATH_NAME => '/DIR/file1.txt'))
Results:
LINE_00001 LINE
1 Data for line 1 is displayed correctly
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSB2F8","label":"DB2 Query Manager and SQL Development Kit for IBM i"},"ARM Category":[{"code":"a8m0z0000001iIgAAI","label":"IBM i Db2->RUNSQL, RUNSQLSTM, STRSQL"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
06 January 2022
UID
ibm16538716