OnDemand User Group

Support Forums => z/OS Server => Topic started by: Steve Lance on January 03, 2019, 09:26:35 AM

Title: SQLR question
Post by: Steve Lance on January 03, 2019, 09:26:35 AM

I have a folder with 2 different Application Groups associated with the folder.  Both Application Groups have the same fields except one has Amount_Charged as decimal and the other has Amount_Charged as string.  As an Administrator when I use the folder I receive in the system log:

Application Group Query: Name(EOB3) Agid(251885) Count() SegStart(2017-07-02 00:00:00) SegStop(2019-01-02 23:59:59) Sql(2;ODDAT_OD_LOAD_DATE,ODDAT_OD_LOAD_DATE;'2017-07-02 00:00:00','2019-01-02 23:59:59';WHERE ODDAT_OD_LOAD_DATE BETWEEN ? AND ? ) SqlR(ODTBL.OD_APPLICATION IN ('ZF120D01    ')) FullTextSearch() FullTextScore() ServerTextSearch() AnnColor() AnnText() OrderBy()

The folder pulls up the documents that are desired.

When a user uses the folder they receive in the system log:

Application Group Query: Name(EOB3) Agid(251885) Count(751) SegStart(2014-01-03 10:12:39) SegStop(2019-01-03 10:12:39) Sql(3;ODDAT_OD_LOAD_DATE,ODDAT_OD_LOAD_DATE,CLAIM_NUMBER;'2014-01-03 10:12:39','2019-01-03 10:12:39';WHERE ODDAT_OD_LOAD_DATE BETWEEN ? AND ? ) ) SqlR(AMOUNT_CHARGED>-.01) FullTextSearch() FullTextScore() ServerTextSearch() AnnColor() AnnText() OrderBy()

and DB2 error in system log:

DB Error: {DB2 FOR OS/390}{ODBC DRIVER}{DSN11015}   DSNT408I SQLCODE = -420, ERROR:  THE VALUE OF A STRING ARGUMENT WAS NOT                   ACCEPTABLE TO THE DECFLOAT FUNCTION                                     DSNT418I SQLSTATE   = 22018 SQLSTATE RETURN CODE                                 DSNT415I SQLERRP    = DSNXRNUM SQL PROCEDURE DETECTING ERROR                     DSNT416I SQLERRD    = -230  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            DSNT416I SQLERRD    = X'FFFFFF1A'  X'00000000'  X'00000000'  X'FFFFFFFF'                  X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                      ERRLOC=5:10:2 -- SQLSTATE=22018, SQLCODE=-420, File=arsdoc.c, Line=3170

Notice the SQLR in both statements.

Any suggestions for a solution?

Title: Re: SQLR question
Post by: Ed_Arnold on January 03, 2019, 11:14:43 AM

Hi Steve - I didn't look up the messages or anything, but DECFLOAT rang a bell.

This is a little old, but make sure you aren't being bitten by this:

http://www.odusergroup.org/forums/index.php?topic=1525.msg5627#msg5627 (http://www.odusergroup.org/forums/index.php?topic=1525.msg5627#msg5627)

Ed

Title: Re: SQLR question
Post by: Steve Lance on January 03, 2019, 11:37:12 AM

That is not the error we are receiving.

Title: Re: SQLR question
Post by: Justin Derrick on January 03, 2019, 12:59:40 PM

Just throwing this out there...  Does the user have a query restriction?

-JD.

Title: Re: SQLR question
Post by: Steve Lance on January 03, 2019, 01:47:59 PM

Sorry for the ignorance, but where can I find that?

Title: Re: SQLR question
Post by: Steve Lance on January 03, 2019, 02:33:26 PM

Started researching and found the SQL statements that were causing the problem.  Corrected and all is good.  Thank you

Title: Re: SQLR question
Post by: Justin Derrick on January 04, 2019, 06:26:33 AM

Hey Steve.

Glad to hear you got it fixed.

Can you give us a little more information about how you solved this issue?  Hopefully so that anyone seeing this post in the future can have enough information to resolve the issue on their own...

Thanks!

-JD.

Title: Re: SQLR question
Post by: Ed_Arnold on January 04, 2019, 08:55:55 AM

Had this come to me I would have started with an ODBC trace:

Quote

To take an ODBC trace:
 Add the following entries [placed in the common section] to the
  version of cli.ini pointed to by the DSNAOINI DD for ARSSOCKD:
  APPLTRACEFILENAME=/tmp/arsodbctrace
  APPLTRACE=1
  An example cli.ini file:
  [COMMON]
  MVSDEFAULTSSID=DSNO
  PLANNAME=DSNACLI
  APPLTRACEFILENAME=/tmp/arsodbctrace
  APPLTRACE=1
  The output can be found in the HFS file pointed to by the
  APPLTRACEFILENAME value.

That should show the exact SQL statement causing the problem.

Ed