[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] SQRW not responding?????
I am no expert in SQR but see if this helps...
After your select statement add move command
count(*) &temp.count
from ps_sss_RES_temp temp
end-select
move &temp.count to #temp.count
and in your "if statement" instead of if &temp.count > 0 say if #temp.count > 0
I am not sure if that would solve your problem but I guess it's worth giving it
a try.
"Katchur, Ken DCRE" <KKatchur@dcre.gov.sk.ca> wrote:
I commented out the call to the procedure to do the count on the
table. And commented out the If statement
so the delete would happen no matter what. This is the SQR log:
SQRW: Structured Query Report Writer V3.0.12.4
Copyright (C) MITI, 1994, 1995. All Worldwide Rights Reserved.
Made it to Init-Report
[2J[1;1HMade it to Delete-Temp
(SQR 5528) ODBC SQL dbexec: SQLExecute error -911 in cursor 4:
[IBM][CLI Driver][DB2/6000] SQL0911N The current transaction has
been rolled back because of
a deadlock or timeout. Reason code "68". SQLSTATE=40001
Error on line 243:
(SQR 3735) Couldn't execute SQL.
SQRW: Program Aborting.
=======================================================================
Line 243 is: delete from ps_sss_RES_temp
I checked in the database and their is rows in that table
Here is the code:
!-------------------------------------------
! Count-Temp
!-------------------------------------------
begin-procedure Count-Temp
#debug do debug-msg('Count-Temp')
display 'Made it to Count-Temp'
begin-select
count(*) &temp.count
from ps_sss_RES_temp temp
end-select
End-Procedure !Count-Temp
!-------------------------------------------
! Delete-Temp
!-------------------------------------------
begin-procedure Delete-Temp
#debug do debug-msg('Delete-Temp')
display 'Made it to Delete-Temp'
!do Count-Temp
!if &temp.count > 0
begin-SQL
delete from ps_sss_RES_temp
end-SQL
!end-if
End-Procedure ! Delete-Temp
From: Larry Roux on 10/30/2003 10:36 AM
Please respond to sqr-users@sqrug.org@SMTP@Exchange
To: sqr-users@sqrug.org@SMTP@Exchange
cc:
Subject: RE: [sqr-users] SQRW not responding?????
It looks like the DB cannot delete the rows because the rows are
locked from the select.
Not sure how DB2 works but can you add a commit after the select?
If the select is using for-update functionality then the select is holding
the row and the delete is waiting for the row to be released before it can
happen.
******************************
Larry Roux
Syracuse University
lroux@syr.edu
*******************************
>>> KKatchur@dcre.gov.sk.ca 10/30/03 11:19AM >>>
I put in some display statements and here is my SQR log.
SQRW: Structured Query Report Writer V3.0.12.4
Copyright (C) MITI, 1994, 1995. All Worldwide Rights
Reserved.
Made it to Init-Report
[2J[1;1HMade it to Delete-Temp
Made it to Count-Temp
(SQR 5528) ODBC SQL dbftch error -911 in cursor 3:
[IBM][CLI Driver][DB2/6000] SQL0911N The current
transaction has
been rolled back because
of a deadlock or timeout. Reason code "68".
SQLSTATE=40001
Error on line 222:
(SQR 3725) Bad return fetching row from database.
SQRW: Program Aborting.
+++++++++++++++++++++++++++++++++++++++++++++++++=
Line 222 is "begin-select" in procedure Count-Temp called by
Delete-Temp
Here is that part of the SQR:
214 !-------------------------------------------
215 ! Count-Temp
216 !-------------------------------------------
217
218 begin-procedure Count-Temp
219 #debug do debug-msg('Count-Temp')
220
221 display 'Made it to Count-Temp'
222 begin-select
count(*) &temp.count
from ps_sss_RES_temp temp
end-select
End-Procedure !Count-Temp
!-------------------------------------------
! Delete-Temp
!-------------------------------------------
begin-procedure Delete-Temp
#debug do debug-msg('Delete-Temp')
display 'Made it to Delete-Temp'
do Count-Temp
if &temp.count > 0
begin-SQL
delete from ps_sss_RES_temp
end-SQL
end-if
End-Procedure ! Delete-Temp
From: Larry Roux on 10/30/2003 08:51 AM
Please respond to sqr-users@sqrug.org@SMTP@Exchange
To: sqr-users@sqrug.org@SMTP@Exchange
cc:
Subject: RE: [sqr-users] SQRW not responding?????
I usually put in display statements at the start of each
procedure -
ie "in proc SELEST-STUDENTS". Once you narrow down which procedure
it is
hanging in then put in displays within each select - ie "before
select
TERMs" and "selected Term" etc.
******************************
Larry Roux
Syracuse University
lroux@syr.edu
*******************************
>>> KKatchur@dcre.gov.sk.ca 10/30/03 09:43AM >>>
Responding to the: Why do you press "Ctrl-Alt-Del?"
I have a Windows 98 desktop and when I do
a"Ctrl-Alt-Del" ,
the task
manager window comes up. It tells which applications are
running.
And I do
this to check SQRW and it says SQRW is not responding.
Before I do
this the
prompt box comes up for me to put in my parameters? I put
them in.
Then
the SQRW prompt is on the task bar. I thought the SQR was
taking
too long.
So I did the "Ctrl-Alt-Del" to see if SQRW was responding.
Responding to the : When you say a different SQR
works, do
you mean
Ctrl-Alt-Del works or the SQR program performs its
task
successfully?
The SQR I am trying to run is SRES2019.sqr. I am
running it
against
datasource (Database) FSCLN, Username dbaprd.
Then I decided to run another sqr SSS2034.sqr
against the
same
Database, Username and password: datasource (Database)
FSCLN,
Username
dbaprd. I did this to see if possibly it was a problem with
the
Database,
my username or password.
Also: Where is the best place to put some display
statements in
the SQR?
Thanks Ken
From: "Alexander, Steve"
on
10/29/2003 05:18 PM
Please respond to sqr-users@sqrug.org@SMTP@Exchange
To: sqr-users@sqrug.org@SMTP@Exchange
cc:
Subject: RE: [sqr-users] SQRW not
responding?????
If the problem is in the compiler or the operating
system,
changing
from
desktop to server might help. If the problem is
slow
database
access, being
on the server will speed that up, but going from
1000 hours
to 100
hours
won't help enough. I'd start by assuming that the
problem
is in my
SQR
program and use some display statements.
I just reread your original message, Ken, and it
suddenly
hit me how
little
information you gave us. Why do you press
"Ctrl-Alt-Del?"
We've
assumed
it's because you're not getting any signs of life
from your
program.
Is
that it or do you just want to interrupt your
program? If
you're
getting no
signs of life, do you mean you get something then
nothing
further,
or you
get nothing at all? When you say a different SQR
works, do
you mean
Ctrl-Alt-Del works or the SQR program performs its
task
successfully?
-----Original Message-----
From: Katchur, Ken DCRE
[mailto:KKatchur@dcre.gov.sk.ca]
Sent: Wednesday, October 29, 2003 2:08 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] SQRW not responding?????
I am running this from my workstation
desktop
(client).
Would it
make a difference if it was run through the server?
Thanks Ken
From: "Alexander, Steve"
on
10/29/2003 03:21 PM
Please respond to
sqr-users@sqrug.org@SMTP@Exchange
To: sqr-users@sqrug.org@SMTP@Exchange
cc:
Subject: RE: [sqr-users] SQRW not
responding?????
This could also be an infinite loop. In
general,
I'd insert
show or
display
statements throughout the program; "show 1",
"show
2", etc.
See
which ones
appear in the SQRw window. That will tell
you how
far your
program
got, and
let you focus on the code that is hanging
up.
-----Original Message-----
From: Larry Roux [mailto:LRoux@syr.edu]
Sent: Wednesday, October 29, 2003 12:46 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] SQRW not
responding?????
This sounds like a runaway select still. If
you do
not have
a
display then
the spf would not get built.
Try having your DBA look to see what the
process is
doing
while the
job is
running. My guess is that it is sticking on
one
statement.
Your
machine
would lock up due to the large result set
filling up
memory.
******************************
Larry Roux
Syracuse University
lroux@syr.edu
*******************************
>>> KKatchur@dcre.gov.sk.ca 10/29/03 02:43PM
>>>
Th .spf file never gets built. The
one from
yesterday is
still
there from when I ran the same SQR against
another
database.
I have
to a
end task to end the SQRW. And sometimes
that does
not work
and my
workstation locks up and I have to do a hard
re-boot.
Thanks Ken
From: "Sahani, Dev"
on
10/29/2003
11:46 AM
Please respond to
sqr-users@sqrug.org@SMTP@Exchange
To:
sqr-users@sqrug.org@SMTP@Exchange
cc:
Subject: RE: [sqr-users] SQRW
not
responding?????
And if that's happening, you will
notice
this .spf
file (in
the
folder that
you are executing the job from)
growing in
size till
the
point that
you kill
the job
-Dev Rishi Sahani
__________________________
FESCo DB Systems -- Development
Fidelity Investments
-----Original Message-----
From: Larry Roux
[mailto:LRoux@syr.edu]
Sent: Wednesday, October 29, 2003
12:36 PM
To: KKatchur@dcre.gov.sk.ca;
sqr-users@sqrug.org
Subject: Re: [sqr-users] SQRW not
responding?????
Could there be a run-away select
statement?
I would
think
that if
you have
a cartesian join somewhere you might
see
that
happen.
******************************
Larry Roux
Syracuse University
lroux@syr.edu
*******************************
>>> KKatchur@dcre.gov.sk.ca 10/29/03
12:17PM
>>>
I am running a SQR through SQRW on
my
desktop and
when I do
a
Crtl+Alt+delete it says SQRW is not
responding.
When I run a different SQR with the
same
database
and
username it
works! Any
help would be appreciated.
Thanks in
advance
Ken
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
---------------------------------
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users