Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: DB2 Cursor Error

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by thangavel_14


    is there any suggestion ?

    Certainly. Read the manual, especially the FETCH statement part.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    Andy

  4. #4
    Join Date
    Mar 2008
    Posts
    4

    DB2 cursor Error

    Dear Friend ,

    I am using DB2 9.1 and windows xp OS .....


    expecting u r reply ......


    Regards,

    Thangavel.L

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You really should read the manual:

    http://publib.boulder.ibm.com/infoce...c/r0000954.htm

    Andy

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated, there is no fetch next statement only fetch from cursorname into
    :hv
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •