Stored Procedure - issue running concurrently

[RogerCruse]

Hi,
All our tables are accessed & updated via views. This means that I can reorganise the databases and keep the backups balanced, etc.

And I've created a stored procedure to COLLECT STATS on the underlying table (the viewname is passed through as a parameter).

This has been working well for the past six months until now...

It seems that, if two batch processes (that have different defaul databases) are using the stored procedure at the same time, one of the processes gets confused about it's default database and fails with a access problem.

If these processes run one at a time, there isn't an issue.

Does this sound familar to anyone?

Have I missed something about stored procedures or is the a bug?

Roger...

 

[dnoeth]

Could you supply more information about that error?

What behaviour do you expect? The default db of the current session should not be used within your SP.

From the manuals:
"The default database is the default database at the time when the stored procedure is created."

replace procedure defdb(out db varchar(30))
begin
sel database into :db;
end;

call teradata_education.defdb(db);

db
------------------------------
TERADATA_EDUCATION

database dbc;

call teradata_education.defdb(db);

db
------------------------------
TERADATA_EDUCATION

Dieter

 

[RogerCruse]

Hi Dieter,
The stored procedure is used to run the same script for different countries - UK & Ireland - and each country havin it's own username with it's own access rights.

That is, the UK process can't read the Irish process and the Irish process can't read the UK.

So, the stored procedure is called explicitly
e.g. call databasename.procedurename ('fred');

Within the the stored procedure it is possible to identify the calling username and hence the calling country and default database (from dbc user details)

As I've mentioned, this works well when the stored procedure is only called by either country but not at the same time.

I've logged the call with NCR and what I need to know is, it this -
1. A known bug with a known fix
2. A known bug that is still being worked on
3. A new bug
4. A typo on my part
5. A fundamental issue with how Stored Procedures work

Roger...

 

[dnoeth]

You extract the default db from dbc.users (instead of using DATABASE) and build a "COLLECT STATS ON.." string to be executed by dbc.sysexecsql?
Do you include fully qualified table names (dbname.tablename) and there's still an error?

If you opened an incident, your NCR support should be able to tell you if this is a known bug...

Dieter

 

[RogerCruse]

Dieter,
Yes, like this -
SELECT DATE, TIME, USER INTO :XSTART_DATE, :XSTART_TIME, :XUSERNAME;

SELECT DEFAULTDATABASE INTO :XVIEWDATABASE
FROM DBC.DBASE
WHERE DATABASENAMEI= :XUSERNAME;

And, Yes. I use fully quanlified names -
CALL DBC.SYSEXECSQL ('COLLECT STATS ' || :XTABLEDATABASE || '.' || :XTABLENAME || ';');

The X variables are DECLAREd and I have a lookup table to tell me the underlying tablename for each viewname (I don't like this bit much and plan to tweak it when I have five minutes!)

Roger...