IDS Forum

Re: RE: error 201 when using functional index

Posted By: LYNKZ MIKE
Date: Friday, 27 November 2009, at 10:49 a.m.

In Response To: Re: RE: error 201 when using functional index (Jonathan Leffler)

Hi Jonatahn, thanks for SQLIDEBUG tip, look at what i found:

The real erorr sis 206 and ISAM 111, what says the data server cant find the table, then shows error 201, then program finish with a dbclose

This is trace with functional indexes created. After trace without functional index.

+14282
+14283 C->S (102) Time: 2009-11-27 10:23:32.00000
+14284 SQ_PREPARE
+14285 # values: 3
+14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and num_edc =? order by cla_ent desc
" [90]
+14287 SQ_NDESCRIBE
+14288 SQ_WANTDONE
+14289 SQ_EOT
+14290
+14291 S->C (20) Time: 2009-11-27 10:23:32.00000
+14292 SQ_ERR
+14293 SQL error..........: -206
+14294 ISAM/RSAM error....: -111
+14295 Offset in statement: 23
+14296 Error message......: "edc_det" [7]
+14297 SQ_EOT
+14298
+14299 C->S (24) Time: 2009-11-27 10:23:32.00000
+14300 SQ_PREPARE
+14301 # values: 0
+14302 CMD.....: "a_pobla_2001" [12]
+14303 SQ_NDESCRIBE
+14304 SQ_WANTDONE
+14305 SQ_EOT
+14306
+14307 S->C (12) Time: 2009-11-27 10:23:32.00000
+14308 SQ_ERR
+14309 SQL error..........: -201
+14310 ISAM/RSAM error....: 0
+14311 Offset in statement: 1
+14312 Error message......: "" [0]
+14313 SQ_EOT

---------------------------------------------------------

TRACE WITHOUT FUNCTIONAL INDEX:

+14282
+14283 C->S (102) Time: 2009-11-27 10:40:02.00000
+14284 SQ_PREPARE
+14285 # values: 3
+14286 CMD.....: "select * from edc_det where cod_emp =? and cod_pto =? and num_edc =? order by cla_ent desc
" [90]
+14287 SQ_NDESCRIBE
+14288 SQ_WANTDONE
+14289 SQ_EOT
+14290
+14291 S->C (284) Time: 2009-11-27 10:40:02.00000
+14292 SQ_DESCRIBE
+14293 Stmt Type...........: 2
+14294 Server Stmt Id......: 49
+14295 Estimated Cost......: 0
+14296 Size of output tuple: 64
+14297 # output fields.....: 12
+14298 Size of string table: 96
+14299 0) Field 'cod_emp'
+14300 Index into string table: 0
+14301 Starting offset in tuple: 0
+14302 Type....................: DECIMAL; NOT NULLABLE
+14303 Length : 2048 (0x800)
+14304
+14305 1) Field 'cod_pto'
+14306 Index into string table: 8
+14307 Starting offset in tuple: 5
+14308 Type....................: DECIMAL; NOT NULLABLE
+14309 Length : 1024 (0x400)

What you think??

Thanks

On Fri, Nov 27, 2009 at 06:51, LYNKZ MIKE <yellr@telecom.com.co> wrote:

> Hi Jonathan, thanks a lot for response, i typo the table name
> The query is :
>
> select * from edcve where cod1=48
>
> We dont build the functional index neither the function for it in 4gl
> application, we built them by dbaccess.
>
> When we drop the functional index the 4GL application does not show the
> error
> 201, then the functional index exists, the application shows error 201.
>
> And even is more rare that application stops with error when opens this
> cursor:
>
> declare cursor1 cursor for select * from edc_det
> where cod_emp = v_emp
> and cod_pto = v_cod_pto
> and num_edc = v_num_edc
> order by cla_ent desc
> end function
>

Wha't the 'end function' doing?

> What it could be?? Its very strange,
>
> How do i do to use SQLIDEBUG=2 ??
>

export SQLIDEBUG=2:something

(the something is used as a prefix to the dump file names).

Then run:

$ dbaccess dbname script

$ minimal-i4gl-program

You should have two files, both starting with 'something'.

sqliprint something_...name1...

sqliprint something_...name2...

Look to see if there's anything startlingly different between what works
(DB-Access) and what doesn't (I4GL). It may be uninformative - but since
-201 syntax error often means a broken SQL statement, you may be able to
spot what is wrong with what the I4GL sent to IDS.

> > Hi Art, thanks a lot for response, i dont understand well, The 4GL
> > application cant run queries on table with functional indexes??
>
> It can run the queries; it just has problems building the functional
> indexes.
>
> > we create the functional indexes using dbaccess just once.
>
> I thought we were looking at I4GL code trying to create the functional
> index.
> If not, then my previous answer was less relevant (even 'less than
> relevant').
>
> > The 4GL compiler syntax checks all SQL and it's parser doesn't understand
> > functional indexes. It only supports 7.3x supported syntax. You can
> prepare
> > and execute the create index as a string or include it in a BEGIN SQL...
> > END SQL block to bypass the parser.
> >
> > Art
> >
> > Subject: error 201 when using functional index [18210]
> >
> > Good afternoon, we are having a rare problem when we are using a
> functional
> > index. There is a 4GL which gives error 201 when there is create a
> > functional
> > index.
> >
> > We are using IDS 10.FC9 on AIX 5.3 and 4GL 7.32
> >
> > The 4GL application get broken if this functional index is created:
> >
> > create table "informix".edcve
> > (
> > cod1 decimal(8,0) not null ,
> > cod2 decimal(4,0) not null
> > );
> >
> > CREATE FUNCTION f_test1( cod1 decimal(8,0) ) RETURNS decimal(8,0)
> > WITH (NOT VARIANT);
> > RETURN cod1 ;
> > END FUNCTION;
> >
> > create index ix_edcve on edcve(f_test1(cod1));
> >
> > update statistics for table edcve;
> >
> > Debugging and using the onmode -I we have found the 4GL stops with error
> > 201
> > when this sql is executed:
> >
> > select * from edc_det where cod_emp =? and cod_pto =? and num_edc =?
> > order by cla_ent desc
> >
>
> This does not reference the table ecdve with the functional index AFAICS.
>
> > When this index is not created the 4GL does not get broken.
> > Before this sql statement the 4GL querys the table in the following way:
> > select * from edcdev where cod1=48
> >
>
> Neither does this query reference the table edcde; it is referencing
> another
> table edcdev.
>
> Are you sure the functional index is the factor?
>
> Ultimately, both DB-Access and I4GL use versions of ESQL/C to access the
> database.
>
> In general, that means they should get similar results. However, DB-Access
> doesn't allow you to play with placeholders where I4GL does, so they are
> not
> completely equivalent.
>
> Sadly, DB-Access uses a different version of ESQL/C (built at the same time
> as the server) from the separate CSDK product. That means there could be a
> bug fix in the DB-Access version that isn't in the I4GL version.
>
> If the problem is consistent, then I would probably want to look at the
> information flowing between the client (DB-Access or I4GL) and the server -
> using SQLIDEBUG=2:something in the environment to capture the
> communications, and then using sqliprint on the log files to compare the
> information flows. You'd want to keep the program/script very small - the
> bare minimum that reproduces the problem.
>
> Typically, this isolates the problem to 'client-side' or 'server-side'.
>
> > Both queries dont get broken if are executed in dbaccess.
> >
> > Anyone knows why is this happening??
>
> No real idea - it shouldn't be happening.
>

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Mike Ditka <http://www.brainyquote.com/quotes/authors/m/mike_ditka.html> -
"If God had wanted man to play soccer, he wouldn't have given us arms."

--000e0cd170f0bb91a504795b94af

Messages In This Thread

• error 201 when using functional index
  LYNKZ MIKE -- Thursday, 26 November 2009, at 3:40 p.m.
  • RE: error 201 when using functional index
    Art S. Kagel -- Thursday, 26 November 2009, at 6:57 p.m.
    • Re: RE: error 201 when using functional index
      LYNKZ MIKE -- Friday, 27 November 2009, at 8:52 a.m.
      • Re: RE: error 201 when using functional index
        Jonathan Leffler -- Friday, 27 November 2009, at 9:25 a.m.
        • Re: RE: error 201 when using functional index
          LYNKZ MIKE -- Friday, 27 November 2009, at 9:51 a.m.
          • Re: RE: error 201 when using functional index
            Jonathan Leffler -- Friday, 27 November 2009, at 10:02 a.m.
            • Re: RE: error 201 when using functional index
              LYNKZ MIKE -- Friday, 27 November 2009, at 10:49 a.m.
              • Re: RE: error 201 when using functional index
                Jonathan Leffler -- Friday, 27 November 2009, at 12:40 p.m.
                • Re: RE: error 201 when using functional index
                  LYNKZ MIKE -- Friday, 27 November 2009, at 2:30 p.m.
      • Re: RE: error 201 when using functional index
        Art Kagel -- Friday, 27 November 2009, at 11:18 a.m.
        • Re: RE: error 201 when using functional index
          LYNKZ MIKE -- Friday, 27 November 2009, at 2:23 p.m.
          • Re: RE: error 201 when using functional index
            Art Kagel -- Friday, 27 November 2009, at 2:46 p.m.
  • Re: error 201 when using functional index
    Jonathan Leffler -- Friday, 27 November 2009, at 9:09 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.