Conference orarep::nomahs::dbintegrator_public_public

    
    Hi Claudia,
    
    We've never seen a problem like this.  Could you post the query?  The
    only problems we've seen when using DBI and ACMS are quota related.
    Did you look up the DSNT4081 problem?
    
    Thanks,
    
    dan

	The error occurs during an open cursor... I'll post the cursor as 
	soon as I get if from customer.

	I did not look for DSN4081, where should I look for it??


				Thanks again

					Claudia

				 

DSNT408I is a rather generic error messgae.  The real info is in the
SQL code -104 and it's text.  The text "illegal symbol," should have shown
what was bad.  Turning on EXPLAIN, DB2_REQ_SQL and ERRORS should trap enough
information to diagnose the problem. Oh yes, the image idents of DBI and the
LDB2 gateway would be helpful too.

A quick guess is that DBI generated a SELECT statement with a WHERE clause
containing a parameter marker in an illegal position.  It would fail in
ACMS because the SQL is not-dynamic, meaning DBI is more likely to use
parameter markers.

I know we would see errors like this during development but they would
always get cleaned up by the time the product shipped.  Maybe one slipped
through.

    
    
    	Here is the log... Guess the problem is that the query is being
    	generated with no balck space between arguments (ORDER BY 1 ASC,2ASC),
    	what generates an error in DB2, is that correct??
    
    
    	Any suggestions?
    
    			Thanks once more
    
    					Claudia
    
    
    
    ***************************************************************************
    
    
---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:14:36.110 1996---

SET TRANSACTION READ_ONLY
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:14:37.047 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:04.059 1996---

ROLLBACK
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:04.059 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:05.182 1996---

SET TRANSACTION READ_ONLY
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:05.182 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:40.120 1996---

ROLLBACK
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:40.121 1996---

---EVENT BEG: EXPLAIN -------------------------- Mon Jul 29 09:15:47.171 1996---

Step              1  At DBI
Cardinality 1  Tuple Length     18  Cost  16.318  +Per Tuple 0
Left Operand      2                       
RESULT

Step              2  At ???
Cardinality 1  Tuple Length     18  Cost  16.318  +Per Tuple 0
STREAM  FROM ??? TO DBI
    SELECT AFM.ORGF_CD_CBI, AFM.AFM_CD_LIVRE, AFM.AFM_NR_SEQUENCIA, 
           AFM.AFM_DT_ANO, AFM.AFM_NR_REVISAO, AFM.EMPR_NR_MATRICULA, 
           AFM.AFM_CD_STATUS
    FROM   AFM T001
    WHERE  ((((AFM.ORGF_CD_CBI = '540'
    AND    AFM.AFM_CD_LIVRE = '10')
    AND    AFM.AFM_NR_SEQUENCIA = '001')
    AND    AFM.AFM_DT_ANO = '96'))
    ORDER BY 001 ASC, 002 ASC

---EVENT END: EXPLAIN -------------------------- Mon Jul 29 09:15:47.217 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:47.247 1996---

SET TRANSACTION READ_ONLY
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:47.247 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:47.129 1996---

SELECT T.ORGF_CD_CBI,T.AFM_CD_LIVRE,T.AFM_NR_SEQUENCIA,T.AFM_DT_ANO,T.AFM_NR_REV
ISAO,T.EMPR_NR_MATRICULA,T.AFM_CD_STATUS FROM MAT.AFM T WHERE ((((T.ORGF_CD_CBI)
=('540') AND (T.AFM_CD_LIVRE)=('10')) AND (T.AFM_NR_SEQUENCIA)=('001')) AND (T.A
FM_DT_ANO)=('96')) ORDER BY 1 ASC,2 ASC 
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:47.131 1996---

---EVENT BEG: ERRORS --------------------------- Mon Jul 29 09:15:47.198 1996---
%LDRV-E-DB2_SUPP,  DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ,
2 VALID SYMBOLS ARE , FOR OPTIMIZE
---EVENT END: ERRORS --------------------------- Mon Jul 29 09:15:47.334 1996---

---EVENT BEG: ERRORS --------------------------- Mon Jul 29 09:15:47.334 1996---
%LDRV-E-SMPL_DB2_ERROR, DB2 error
---EVENT END: ERRORS --------------------------- Mon Jul 29 09:15:47.335 1996---

---EVENT BEG: ERRORS --------------------------- Mon Jul 29 09:15:47.382 1996---
%RDB-F-IO_ERROR, input or output error
---EVENT END: ERRORS --------------------------- Mon Jul 29 09:15:47.382 1996---

---EVENT BEG: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:55.188 1996---

ROLLBACK
---EVENT END: DB2_REQ_SQL ---------------------- Mon Jul 29 09:15:55.189 1996---

Nothing looks out of sorts to me.

My questions are:

1) Which DB2 Version is this?

2) Is the first column in the order by (ORGF_CD_CBI) a long varchar?

	DB2 version is 3.1.0 and this field is a CHAR(3).

	What about the parameter in the order by coming with no 
	blank space after the ','?  The IBM side of customer support
	says that is the problem - he says DB2 needs the blank space.


				Thanks once more

						Claudia

	Just one more information - it works fine on a VAX (same
	DB2 database - they just migrate).


				Claudia

I'm pretty sure white space is not the issue, although if you believe
the test of the -104 error message it does look that way.  Just to test it
out I tried a similar query using the ISPF SPUFI interface and it accepted
the lack of white space just fine.  I posted the proof at the end of this
note.

Suggestions:

1) If it works fine on a VAX then can you post the DB2_REQ_SQL EXPLAIN ERRORS
   log for when it works.

2) Have the customer try the failing query in SPUFI.  Make sure to enter
   it exactly as it appears in the DB2_REQ_SQL output.  If it fails there
   the try adding the white space and see if that corrects the problem.

********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM DBI1L700.S ORDER BY 1 ASC,2 ASC;
---------+---------+---------+---------+---------+---------+---------+---------+
SNUM   SNAME                 STATUS  CITY
---------+---------+---------+---------+---------+---------+---------+---------+
S1     Smith                     20  London
S2     Jones                     10  Paris
S3     Blake                     30  Paris
S4     Clark                     20  London
S5     Adams                     30  Athens
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+

    
    
    
    
    	Thanks once more for helping!!! We can not run on the VAX anymore
    	because it was just disconnected... 
    
    	I've asked customer to test with the SPUFI, but he said it would
    	be easier with QMF (once I know nothing of IBM, I don't know
    	exactly whatthe differeces are - but here is the log he sent
    	me).
    
    *************************************************************************

	1) ERROR (Without blank space)
    
    *************************************************************************

 SQL QUERY                                               MODIFIED    LINE 1
 
 SELECT T.ORGF_CD_CBI,T.AFM_CD_LIVRE,T.AFM_NR_SEQUENCIA,T.AFM_DT_ANO,
 T.AFM_NR_REVISAO,T.EMPR_NR_MATRICULA,T.AFM_CD_STATUS FROM MAT.AFM T
  WHERE ((((T.ORGF_CD_CBI)=('540') AND (T.AFM_CD_LIVRE)=('10')) AND
 (T.AFM_NR_SEQUENCIA)=('001')) AND (T.AFM_DT_ANO)=('96'))
  ORDER BY 1 ASC,2 ASC
 
 
 
 
 
 
 
 
 
 
 QUERY MESSAGES:
 SQL error at or before ,2  (line 5, position 16).
 *** END ***
 1=Help       2=Run        3=End         4=Print     5=Chart       6=Draw
 7=Backward   8=Forward    9=Form       10=Insert   11=Delete     12=Report
 The query did not run.  See QUERY panel for error messages.
 COMMAND ===>                                                   SCROLL ===> PAGE

    *************************************************************************

	2) NO ERROR  (blank space included): 

    *************************************************************************


 SQL QUERY                                               MODIFIED    LINE 1
 
 SELECT T.ORGF_CD_CBI,T.AFM_CD_LIVRE,T.AFM_NR_SEQUENCIA,T.AFM_DT_ANO,
 T.AFM_NR_REVISAO,T.EMPR_NR_MATRICULA,T.AFM_CD_STATUS FROM MAT.AFM T
  WHERE ((((T.ORGF_CD_CBI)=('540') AND (T.AFM_CD_LIVRE)=('10')) AND
 (T.AFM_NR_SEQUENCIA)=('001')) AND (T.AFM_DT_ANO)=('96'))
  ORDER BY 1 ASC, 2 ASC
 
 
 
 
 
 
 
 
 
 
 *** END ***
 
 
 1=Help       2=Run        3=End         4=Print     5=Chart       6=Draw
 7=Backward   8=Forward    9=Form       10=Insert   11=Delete     12=Report
 OK, QUERY is displayed.
 COMMAND ===>                                                   SCROLL ===> PAGE
    
    
********************************************************************************
    
    
    
    	Hope that's what you were looking for...
    
    	
    
    				Thanks again
    
    					Claudia

    
    Hi Claudia,
    
    We are looking into adding white space for the next version of DBI. 
    For now, you may want to check with the IBM folks - there may be a bug
    fix available for this from them (we are generating standard SQL, and
    white space is not required where they are looking for it according to
    the SQL-92 spec in my office.
    
    Thanks,
    
    dan

	Dan,


	Thanks a lot!!!  We will try contacting IBM to know if they 
	some workaround.  I'll open a former call too, just so we have
	"an offcial" answer from Oracle to give to the customer - this 
	will be abig deal. This is just Brazil's largest customer and
	they have just migrate all they VAX environments to AXP, so 
	guess what...


			Thanks a lot once more

					Claudia

    
    Hi Claudia,
    
    Just to follow up - in case IBM comes up empty handed, one of the
    engineers here has checked in code to add white space between the order
    by columns.  It will be available in DBI7 (FT3 or greater).
    
    Dan