Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    15

    Unanswered: DB2 throwing an error while increasing the column length

    Hi All,
    I am facing problem while increasing the column length on one table.
    Please find the below error
    DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 3;-551;42501;BFEPRE1|SELECT|XXX
    Message: An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "BFEPRE1|SELECT|XXX". 

    I am able to add the column and drop the table. but I am not able to increase the column length. Please help to suggest for solution.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I presume, you use alter table
    -551 = missing authority
    look at alter command in infocenter and see for needed authorities
    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

  3. #3
    Join Date
    Oct 2013
    Posts
    15
    Hi przytula_guy,
    But I am able to drop table and add column. I am using instance id

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    display your authorities - db - table
    db2level-platform
    executed command - error
    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

  5. #5
    Join Date
    Oct 2013
    Posts
    15
    Quote Originally Posted by przytula_guy View Post
    display your authorities - db - table
    db2level-platform
    executed command - error
    Hi przytula,
    I have SYSADM authority.

    AUTHORITY D_USER D_GROUP D_PUBLIC ROLE_USER ROLE_GROUP ROLE_PUBLIC D_ROLE
    ------------------------- --------- ---------- ----------- ------------ ------------- -------------- ---------
    BINDADD N N N N N N *
    CONNECT N N N N N N *
    CREATE_EXTERNAL_ROUTINE N N N N N N *
    CREATE_NOT_FENCED_ROUTINE N N N N N N *
    CREATETAB N N N N N N *
    DBADM N N N N N N *
    IMPLICIT_SCHEMA N N N N N N *
    LOAD N N N N N N *
    QUIESCE_CONNECT N N N N N N *
    SECADM N N N N N N *
    SYSADM * Y * * * * *
    SYSCTRL * N * * * * *
    SYSMAINT * N * * * * *
    SYSMON * N * * * * *

    14 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

    [Executed: 6/3/14 8:54:13 AM GMT ] [Execution: 14/ms]

    db2 level is
    $ db2level
    DB21085I Instance "bfeinst1" uses "64" bits and DB2 code release "SQL09058"
    with level identifier "06090107".
    Informational tokens are "DB2 v9.5.0.8", "s110603", "IP23263", and Fix Pack
    "8".
    Product is installed at "/opt/IBM/db2/V9.5".

    EXECUTED COMMAND
    "ALTER TABLE tname ALTER COLUMN cname SET DATA TYPE VARCHAR(780)"

    Error:
    >[Error] Script lines: 1-1 --------------------------
    DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 3;-551;42501;BFEPRE1|SELECT|TNAME
    Message: An error occurred during implicit system action type "3". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "BFEPRE1|SELECT|TNAME" 

    [Executed: 6/3/14 8:56:32 AM GMT ] [Execution: 0/ms]

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you don not have dbadm
    authority on table ?
    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

  7. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    it complains about lack of SELECT privilege on TNAME for user BFEPRE1.
    1. Is this a table or view object in the message "BFEPRE1|SELECT|TNAME"?
    2. Are there "tname" in the ALTER statement and "TNAME" in the message above the same objects?
    3. IF TNAME is a view, then what is the result of:
    Code:
    select count(1)
    from syscat.tabauth
    where tabschema='SCHEMA_PART_OF_TNAME' and tabname='TABLE_PART_OF_TNAME' 
    and grantee='BFEPRE1' and granteetype='U'
    Regards,
    Mark.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The SYSADM privilege fro BFEPRE1 is granted via a group, which would not be effective in static context. You'd need to grant the required privileges to the user directly, or log in as another user that has these privileges.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Oct 2013
    Posts
    15
    Hi,
    tname is a table.
    here I dont understand why BFEPRE1 came into picture.
    I took the db2look for My table . here I got some information about
    BFEPRE1 .
    SET CURRENT SCHEMA = "MYSCHEMA";
    SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","BFEPR E1";

    Please help me to understand root cause for this error.

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    do not jump around - focus first on the original problem
    have you tested the comment from Nick : You'd need to grant the required privileges to the user directly, or log in as another user that has these privileges.
    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

  11. #11
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Probably there are some invalid objects dependent on this table.
    Like triggers, routines, views. These objects might be created by user BFEPRE1.
    DB2 tries to revalidate implicitly such object, and since user BFEPRE1 doesn't have SELECT privilege on table TNAME at the moment, the "alter" operation fails.
    To investigate the problem you may analyze all these invalid dependent objects or just run "GRANT SELECT ON TABLE TNAME TO USER BFEPRE1" and try to alter this table column again.
    Regards,
    Mark.

  12. #12
    Join Date
    Oct 2013
    Posts
    15
    Hi All,

    I have granted the select access to that user on dependent tables. I am able do the alter operation.
    Thanks alot to every one who involved on this solution.

    Regards,
    Bkumar

Posting Permissions

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