Author Topic: arsdoc query is failing with SQL0420N Invalid character found in a character st  (Read 641 times)

mayank81089

  • Jr. Member
  • **
  • Posts: 21
    • View Profile
Hi Team,
We are currently facing issue with one of our scripts. The primary area in which we are seeing this issue is the "arsdoc" query when it gets/extracts the documents from disk.
The snippet goes like below:

 

    /opt/IBM/ondemand/V10.5/bin/arsdoc \
      get -h $HOSTNAME \
          -f "$FOLDER" \
          -i "WHERE tstcrt_number||cast_number in (select tstcrt_number||min(cast_number) from root.$TABLE group by tstcrt_number) AND ARCHIVE_DATE = '$PREVMAXEXT' AND BS_CSI_No = '$CSI'" \
          -a \
          -d $WORK_DIR \
          -o ctbodctc.afp \
          -u dsgrep \
          -p /opt/IBM/ondemand/V10.5/config/ars.stash \
          -v >> $LOG 2>&1

 

Note : This arsdoc function gets/extracts data based on a filter criteria that takes customer_number into consideration.

 

Please note that the sql query in above arsdoc function is using "concat" or "||". The exact same query is running perfectly fine on AIX in our current production system.
However, when running this in Linux system in our new production machine



Error in Linux :
2023-03-31 20:19:04.331935: ARS6117I Querying database with SQL string 'WHERE concat(tstcrt_number,cast_number) in (select concat(tstcrt_number,cast_number) from root.RQD1) AND ARCHIVE_DATE = '03/30/2023' AND BS_CSI_No = '3147843''

 

2023-03-31 20:19:04.354335: ARS6085E Search unsuccessful

 

2023-03-31 20:19:04.354406: ARS6034W No hits were found that matched search criteria.

 

2023-03-31 20:19:04.354406: ARS6011E The error occurred on line number 1.

 

2023-03-31 20:19:04.354971: ARS6026I arsdoc completed.
---------------------------------------------------------------------------------------------------
Error in ondemand client :
2023-03-31 23:49:04.238311    DSGREP      130835221    Error    No        13    DB Error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0420N  Invalid character found in a character string argument of the function "DECFLOAT".  SQLSTATE=22018  -- SQLSTATE=22018, SQLCODE=-420, File=arsdoc.c, Line=3563    system

Lars Bencze

  • Full Member
  • ***
  • Posts: 116
  • CMOD Expert at Skandia
    • View Profile
    • INACTIVE - Bezland Consulting
Hi Mayank,

Did you by any chance alter the data type of any of the fields in Production to DecFloat (or any other numerical data type)?
Since concat takes two strings and puts them together, I assume that tstcrt_number, cast_number and BS_CSI_No are all strings.
Another thing to check here, if for example BS_CSI_No is a numeric value, then your SQL should not have 'quotes' around its value.
OnDemand for MP expert. #Multiplatforms #Admin #Scripts #Performance #Support #Architecture #PDFIndexing #TSM/SP #DB2 #CustomSolutions #Integration #UserExits #Migrations #Workflow #ECM #Cloud #ODApi

mayank81089

  • Jr. Member
  • **
  • Posts: 21
    • View Profile
Thanks Lars,

Actually the problem was with the date format we figured it out and it resolved the problem

Lars Bencze

  • Full Member
  • ***
  • Posts: 116
  • CMOD Expert at Skandia
    • View Profile
    • INACTIVE - Bezland Consulting
Thanks Lars,

Actually the problem was with the date format we figured it out and it resolved the problem
Glad to hear that you solved it - thanks for sharing the solution here!
OnDemand for MP expert. #Multiplatforms #Admin #Scripts #Performance #Support #Architecture #PDFIndexing #TSM/SP #DB2 #CustomSolutions #Integration #UserExits #Migrations #Workflow #ECM #Cloud #ODApi