DB2 Cursor Error

thangavel_14 · #1 (**permalink**) 07-21-08, 06:20

Dear Techies,

I have created creadted the following DB2 procedure ,

Create procedure db2admin.calc_values( IN @app_key varchar(50))
LANGUAGE SQL
BEGIN
DECLARE @risk DECIMAL;
DECLARE @techqual DECIMAL;
DECLARE @bizqual DECIMAL;
DECLARE @temp_val DECIMAL;
DECLARE @cnt DECIMAL;
DECLARE @src varchar(40);
DECLARE @platform_type DECIMAL;
DECLARE @srcavail DECIMAL;
DECLARE @supportrisky DECIMAL;
DECLARE @loc DECIMAL;
DECLARE @owner DECIMAL;
DECLARE @design_extensible DECIMAL;
DECLARE @functionality_reused DECIMAL;
DECLARE @deployment_ease DECIMAL;
DECLARE @secure DECIMAL;
DECLARE @workflow_defined DECIMAL;
DECLARE @transaction_based DECIMAL;
DECLARE @rule_based DECIMAL;
DECLARE @data_interchanges_std DECIMAL;
DECLARE @data_format_std DECIMAL;
DECLARE @dependencies_defined DECIMAL;
DECLARE @dependencies_minimal DECIMAL;
DECLARE @internal_interactions_intf DECIMAL;
DECLARE @external_interaction_intf DECIMAL;
DECLARE @remotely_admin DECIMAL;
DECLARE @monitored_auto DECIMAL;
DECLARE @maintained_easily DECIMAL;
DECLARE @infrastructure_maint DECIMAL;
DECLARE @tech_obsolete DECIMAL;
DECLARE @technology_std DECIMAL;
DECLARE @support_risky DECIMAL;
DECLARE @critical DECIMAL;
DECLARE @metrics_avail DECIMAL;
DECLARE @reports_prsent DECIMAL;
DECLARE @source_code DECIMAL;
DECLARE @platform DECIMAL;
DECLARE @app_scalable DECIMAL;
DECLARE @available DECIMAL;
DECLARE @high_perform DECIMAL;
DECLARE @reliable DECIMAL;
DECLARE @disaster_reco DECIMAL;
DECLARE @func_imp DECIMAL;
DECLARE @produvtivity DECIMAL;
DECLARE @scalable DECIMAL;
DECLARE @availablilty DECIMAL;
DECLARE @dataclean DECIMAL;
DECLARE @time_to_market DECIMAL;
DECLARE @reduce_cost DECIMAL;
DECLARE @reduce_effort DECIMAL;
DECLARE @end_to_endfunc DECIMAL;
DECLARE @response_time DECIMAL;
DECLARE @navigation DECIMAL;
DECLARE @intuitive DECIMAL;
DECLARE @artifactnum Integer;
DECLARE @location varchar(40);

DECLARE artifact_cursor CURSOR FOR SELECT artifactnum,location FROM Artifact_tab WHERE action != 'D' AND artifactype LIKE @app_key ;
OPEN artifact_cursor;

FETCH NEXT FROM artifact_cursor into @artifactnum, @location;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @techqual = 0;
SET @bizqual = 0;
SET @platform_type = 1;
SET @srcavail = 1;
SET @supportrisky = 1;
SET @loc = 2;
SET @owner = 1;
SET @design_extensible = 0;
SET @functionality_reused = 0;
SET @deployment_ease = 0;
SET @secure = 0;
SET @workflow_defined = 0;
SET @transaction_based = 0;
SET @rule_based = 0;
SET @data_interchanges_std = 0;
SET @data_format_std = 0;
SET @dependencies_defined = 0;
SET @dependencies_minimal = 0;
SET @internal_interactions_intf = 0;
SET @external_interaction_intf = 0;
SET @remotely_admin = 0;
SET @monitored_auto = 0;
SET @maintained_easily = 0;
SET @infrastructure_maint = 0;
SET @tech_obsolete = 0;
SET @technology_std = 0;
SET @support_risky = 0;
SET @critical = 0;
SET @metrics_avail = 0;
SET @reports_prsent = 0;
SET @source_code = 0;
SET @platform = 0;
SET @app_scalable = 0;
SET @available = 0;
SET @high_perform = 0;
SET @reliable = 0;
SET @disaster_reco = 0;
SET @func_imp = 0;
SET @produvtivity = 0;
SET @scalable = 0;
SET @availablilty = 0;
SET @dataclean = 0;
SET @time_to_market = 0;
SET @reduce_cost = 0;
SET @reduce_effort = 0;
SET @end_to_endfunc = 0;
SET @response_time = 0;
SET @navigation = 0;
SET @intuitive = 0;

IF EXISTS (select * from application_questionaire where artifactnum = @artifactnum )
BEGIN
select @design_extensible=design_extensible, @functionality_reused=functionality_reused, @deployment_ease=deployment_ease, @secure=secure, @workflow_defined=workflow_defined, @transaction_based=transaction_based, @rule_based=rule_based, @data_interchanges_std=data_interchanges_std, @data_format_std=data_format_std, @dependencies_defined=dependencies_defined, @dependencies_minimal=dependencies_minimal, @internal_interactions_intf=internal_interactions_ intf, @external_interaction_intf=external_interaction_in tf, @remotely_admin=remotely_admin, @monitored_auto=monitored_auto, @maintained_easily=maintained_easily, @infrastructure_maint=infrastructure_maint, @tech_obsolete=tech_obsolete, @technology_std=technology_std, @support_risky=support_risky, @critical=critical, @metrics_avail=metrics_avail, @reports_prsent=reports_prsent, @source_code=source_code, @platform=platform, @app_scalable=app_scalable, @available=available, @high_perform=high_perform, @reliable=reliable, @disaster_reco=disaster_reco, @func_imp=func_imp, @produvtivity=produvtivity, @scalable=scalable, @availablilty=availablilty, @dataclean=dataclean, @time_to_market=time_to_market, @reduce_cost=reduce_cost, @reduce_effort=reduce_effort, @end_to_endfunc=end_to_endfunc, @response_time=response_time, @navigation=navigation, @intuitive=intuitive from application_questionaire where artifactnum = @artifactnum ;

set @techqual= @design_extensible*0.25+ @functionality_reused*0.25+ @deployment_ease *0.25+ @secure*0.25 ;
set @techqual= @techqual+ @workflow_defined*.11+ @transaction_based *.11+ @rule_based *.11+ @data_interchanges_std *.11+ @data_format_std *.11+ @dependencies_defined *.11+ @dependencies_minimal *.11+ @internal_interactions_intf *.11+ @external_interaction_intf *.11;
set @techqual= @techqual+ @remotely_admin*0.1+ @monitored_auto*0.1+ @maintained_easily*0.1+ @infrastructure_maint*0.1+ @tech_obsolete*0.1+ @technology_std*0.1+ @support_risky*0.1+ @critical*0.1+ @metrics_avail*0.1+ @reports_prsent*0.1;
set @techqual= @techqual+ @scalable*0.2+ @Availablilty*0.2+ @high_perform*0.2+ @reliable*0.2+ @disaster_reco*0.2;
set @techqual= (@techqual/4)
set @bizqual = @app_scalable*0.33+ @available*0.33+ @func_imp*0.5+ @produvtivity*0.5+ @dataclean*0.33+ @time_to_market*0.33+ @reduce_cost*0.33+ @reduce_effort*0.33+ @end_to_endfunc*0.25+ @response_time*0.25+ @navigation*0.25+ @intuitive*0.25;
set @bizqual= (@bizqual /4);

set @platform_type= @platform;
set @srcavail = @source_code;
set @supportrisky = @support_risky;
set @src = 'User';

END
ELSE
BEGIN
set @bizqual=1;
set @techqual=1;
set @src = 'System';
END

select @cnt=count(*) from Ownership_tab where artifactnum= @artifactnum ;
if @cnt > 0
set @owner =4;
else
set @owner= 1;

IF (@location = 'Domestic' )
set @loc = 4;

set @risk = (@platform_type + @srcavail + @supportrisky + @owner + @loc)/5;

select @cnt = count(*) from Quality_tab where artifactnum= @artifactnum;

if (@cnt = 0)
insert into Quality_tab(artifactnum,created_on,riskquality,bus inessquality,technicalquality,source) values(@artifactnum,CURRENT TIMESTAMP ,@risk,@bizqual,@techqual,@src);
else
update Quality_tab set created_on=CURRENT TIMESTAMP, riskquality = @risk, businessquality=@bizqual,technicalquality=@techqua l,source=@src where artifactnum= @artifactnum;

FETCH NEXT FROM artifact_cursor into @artifactnum, @location;

END

CLOSE artifact_cursor;
DEALLOCATE artifact_cursor;

END
@

i am getting the following :::::::

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "NEXT" was found following "fact_cursor;
FETCH". Expected tokens may include: "<space>". LINE NUMBER=64.
SQLSTATE=42601

SQL0104N An unexpected token "NEXT" was found following "fact_cursor;

FETCH". Expected tokens may include: "<space> ".

Explanation:

A syntax error in the SQL statement or the input command string
for the SYSPROC.ADMIN_CMD procedure was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement or the input command
string for the SYSPROC.ADMIN_CMD procedure that preceded the
token that is not valid.

As an aid, a partial list of valid tokens is provided in the
SQLERRM field of the SQLCA as "<token-list>". This list assumes
the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

is there any suggestion ?

Thanks in advance ....

Thangavel.L