Ingres on HP Unix

[Rajiv_1]

Hi

Setup :-
Backend - Ingres database on HP-UX B.10.20
Frontend - Powerbuilder app that extracts data from Ingres.

Problem -
In one of the modules on Powerbuilder App.
that extracts data from a particular table the data is not being displayed after a certain point/certain date.
This module has been running fine since a long time.

Solutions tried so far :-
Indexed the Ingres Database again.
Ran sysmod.

After trying the above, no successful results were achived.

Thanks for your help in advance.

Rajiv.

[Justo Exposito]

Hi Rajiv,

Do you try the same select at the unix box with the isql tool?

Do you check the time zone variable in the unix box?

Regards,

Justo.

[Rajiv_1]

Hi

Thanks for your reply.
I already tried running the same SQL query from Ingres-ingmenu, it displays the correct resultset.

Also i checked the time zone (TZ) variable, it displays the correct value.
TZ=EST5EDT ( I am in Eastern Zone )

In addition i deleted the old data from this table and reindexed it..but didn't help.

How does Time Zone variable has any association with it ?

regards
Rajiv.

[Justo Exposito]

Hi Rajiv,

I think that if you obtain the correct results running the select by the ingmenu utility, the problem is between the Ingres server and the Powerbuilder App (The client).
Perhaps somebody change the II_DATE_FORMAT variable or in PowerBuilder some variable like that, I don't know PowerBuilder I was working more than 10 years with Ingres.

The PowerBuilder are running in the Unix Box or is runing in a NT?

Regards,

Justo.

[Justo Exposito]

Hi Again,

I'm thinking that perhaps you can keep the exact select that PowerBuilder is sending to Ingres by using the IPM tool at the Unix Box.

I think that this is a date conversion problem between PowerBuilder and Ingres, then you must look for the exact select that PowerBuilder send to Ingres and look at the date variable and the format that this have.

Regards,

Justo.

[Rajiv_1]

Hi Again

Are you telling me to capture the sql command being sent from Powerbuilder to Ingres using IPM Utility.
How can i capture it using IPM ?

Also since the date after which the records are not being sent to powerbuilder is 080602
which is in between a month and the query on PB is being performed by the Event Number and not by date do you still believe that there is a problem with the date ?


Thanks for your help.

regards
Rajiv.

[Justo Exposito]

Hi,

Wich kind of format is the field in the table, is varchar, char, date, integer?

080602 is August 6th of 2002? or is June 8th of 2002?

Regards,

Justo.

[Justo Exposito]

As I can remenber you can catch the query by using IPM, you can do it by choosing the dbms server, then it shows the sessions connected to the DBMS and then you have a function key for details.

Regards,

Justo.

[Rajiv_1]

Hi

Column Name Data Type Key # Nulls Defaults
exc_id i4 1 no no
exc_date date no no

080602 is August 6th of 2002.

regards
Rajiv.

[Justo Exposito]

And wich is the select command used by you and by power Builder?

If you try in ingmenu to look for data with the condition 060802, there are rows selected?

Regards,

Justo.

[Rajiv_1]

Hi again

Thanks for so much help for trying to solve this problem.

I could not find the variable II_DATE_FORMAT.

The PowerBuilder is running from NT.

Catching Query using IPM..
After chossing the DBMS server and then the session, I chose 1 for more info and 1 again for more info..it shows a blank window with the heading 'Query Text'

Select Statement in PB

Select
fieldnames
from
tablename
where
except_id = :passed_key

Select Statement in Unix
select *
from
tablename
where
exc_id >= 34187
and
exc_id <= 34199

OR

select *
from
tablename
where
exc_date >= '08-Aug-2002'

will result in the same data being displayed.


Rajiv.

[Rajiv_1]

Hi again

I am thinking that since the query parameters are Unique numbers and not date, aren't we heading in the wrong direction by looking at the problems associated with the date and not by some other thing.

Also i want to tell you that we are deleting certain records mannually everyday that are not meant for going into this application.
On the day after which this problem started the records were not deleted before the system processed it. It resulted into 2169 additional records being appended to this table.
I later mannualy deleted these records and reindexed this table.
( This table has a total of 28298 records and 7 columns as on now )

regards
Rajiv.

[Justo Exposito]

Hi Rajiv,

Yes as you tell the problem isn't it the date format or something related with the date.

Perhaps the problem are with the data deleted and the field esc_id and the consistency for this key.

You must study the problem and check the data and the query from Ingres and from PowerBuilder and how this tool create and send the select statment to the Ingres server.

Regards,

Justo.