Why do some SQL results show pointer?

Why do some SQL results show pointer?

Almost all of us have encountered this situation once in their life time that we are querying some table on IBM i and observed the data shown as *POINTER from STRSQL interface in 5250 emulator session and when you look it from ACS Run SQL Script it is not the case.

Yes, *POINTER is displayed in STRSQL when it cannot interpret the data from some of the particular data type such as BLOB, CLOB, and DBCLOB.

Just take an example to visualize this, when we use IFS_Read() table function it returns the data from the ifs file in a CLOB type.

Execute the below SQL query from STRSQL interface on 5250 emulator session

select line from table(qsys2.ifs_read('/home/easyclass/test1.txt')) 

Note:here you can pass the ifs file path available at your end.

The result shown as *POINTER in the STRSQL interface.

LINE                            
*POINTER                        
*POINTER                        
********  End of data  ******** 

Now execute the same SQL query from the ACS Run SQL Script

select line from table(qsys2.ifs_read('/home/easyclass/test1.txt')) 

Note:here you can pass the ifs file path available at your end.

The result shown on the ACS Run SQL Script session as follows:

LINE                            
LIB1                       
LIB2                       
********  End of data  ******** 

Now, if you want to display the results on the STRSQL interface then you can cast the column to VARCHAR as below.

SELECT cast(line as varchar(100)) LINE FROM table(qsys2.ifs_read('/home/easyclass/test1.txt'))

The result can be seen now as what we can see on the ACS Run SQL Script.

LINE                            
 LIB1                           
 LIB2                           
********  End of data  ******** 

Note: I would suggest that we should try to use ACS Run SQL Script for running and SQL query as it does not allows to do extra things like casting and shown us the results what we needed.