Thursday, July 23, 2009

Apply / Activate make Siebel Tools hang ??

Something strange happened with me today and it seems to be a issue with the Siebel Tools that whenever I tried "Apply/Activate" a table into my Siebel Local Database, Tools gets hanged. Frustration !!! I can not continue with my work as I need to add a new field into the business component and see it on the UI.

Checked the support web for this kind of behaviour and found that during "Apply/Activate" Siebel create a table with name "D_U_M_M_Y" into the local database and we need to drop that table from local database before doing any more "Apply/Activate".

I tried connecting to the Local Database via DBISQLC and ran the below query :
select * from siebel.D_U_M_M_Y

but I got the error saying "Table siebel.D_U_M_M_Y not found".
So not able to find the correct resolution as of now.

So, the only workaround I can think of is applying the physical database changes manually. Here is below I did:

1. I need to add one column in S_EVT_ACT_X table of Varchar2(15 Char).
2. Added the column in Siebel Tools under S_EVT_ACT_X table.
3. Ran the following command in DBISQLC (after connecting to the local database) :

alter table S_EVT_ACT_X
add NEWCOLUMN VARCHAR(15)

This way I achieved what I was trying to do. There is something more of it I would like to tell you here which might be useful in case you want to apply/activate a complete new customized table.

Consider a scenario, one of your colleague (Siebel Developer) has created a new Customized table for some requirement and put in his changes into the Siebel Database and now you to have that table available into your local database as well. You can "GET" the project to see the logical schema for the table into the Local Database but the problem is "Apply/Activate" is not working. So here is one workaround I can suggest :

1. Get the DDL for the table via connecting Oracle SQL Developer or TOAD or any other database client you use.
2. Open DBISQLC and run the "Create Table" command (some modifications needs to apply before running the query)


Here below is the example that might be helpful for the modifications required :
I got the below DDL for one of the table :

CREATE TABLE "SIEBEL"."CX_NEW_TABLE"
( "ROW_ID" VARCHAR2(15 CHAR) NOT NULL ENABLE,
"CREATED" DATE DEFAULT sysdate NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(15 CHAR) NOT NULL ENABLE,
"LAST_UPD" DATE DEFAULT sysdate NOT NULL ENABLE,
"LAST_UPD_BY" VARCHAR2(15 CHAR) NOT NULL ENABLE,
"MODIFICATION_NUM" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,
"CONFLICT_ID" VARCHAR2(15 CHAR) DEFAULT '0' NOT NULL ENABLE,
"NEWCOLUMN1" DATE, "NEWCOLUMN2" CHAR(1 CHAR),
"NEWCOLUMN3" VARCHAR2(250 CHAR),
"NEWCOLUMN4" VARCHAR2(50 CHAR),
)


but if you try running this query directly into DBISQLX you might get errors. So below are the changes required :

a) Replace VARCHAR2(15 Char) to VARCHAR(15)
b) Replace CHAR(1 CHAR) to CHAR
c) Replace Number(10,0) to Integer
d) Replace sysdate to getdate()
e) Remove "Enable" from the query.

Finally the new query will look like :

CREATE TABLE "SIEBEL"."CX_NEW_TABLE"
( "ROW_ID" varchar(15) NOT NULL,
"CREATED" DATE DEFAULT getdate() NOT NULL,
"CREATED_BY" varchar(15) NOT NULL ,
"LAST_UPD" DATE DEFAULT getdate() NOT NULL ,
"LAST_UPD_BY" varchar(15) NOT NULL ,
"MODIFICATION_NUM" Integer DEFAULT 0 NOT NULL ,
"CONFLICT_ID" varchar(15) DEFAULT '0' NOT NULL,
"NEW COLUMN 1" DATE, "NEW COLUMN 2" CHARACTER,
"NEW COLUMN 3" varchar(250),
"NEW COLUMN 4" varchar(50),
)


After making all the changes mentioned above run the query in DBISQLC and it works fine.
As I already mentioned this is just a workaround that I can think of, if you have some better idea for this, please let me know as well :). Your comments are most welcome !!
.

21 comments:

  1. If you have siebel client or dbisqlc open then you try to apply and activate you siebel tools will hang.

    The reason for this is that both these things lock the DBF and no other changes are allowed.

    ReplyDelete
  2. Also increasing the size of Virtual memory will instantly solve the problem.
    It worked for me.

    ReplyDelete
  3. Neel, We killed all the dbeng.exe throught task manager restarted the siebel tools still it was hanging. Not sure what was the reason also no dbisqlc was running at that time.

    ReplyDelete
  4. Hi there
    I have a question,In Check-in Checkout where we have activated single object Checkout rather than full project checkout... in this case... if somebody wants to create a new object on dev server,how will it be spread to other developer's local database?any idea?

    Thanks and regards,
    Ashay

    ReplyDelete
  5. As far as the best practice is concern, once Developer will check-in the newly created new object, all the other developers should do a "GET" of that project, simple.

    ReplyDelete
  6. @George

    I increaed the Virtual Memory from 3048 to 6000MB and tried Apply/Activate and it worked for me now.

    Thankyou George !!

    ReplyDelete
  7. You don't need to kill dbengine.exe.

    It is required to make local work.. just make sure that no client or dbisqlc.exe is open.

    so you need to check for siebel.exe in task manager

    ReplyDelete
  8. I added virtual memory (6000 Mb in drive C and 10000 Mb in drive D); I don't have any instance of web client or interactive SQL opened; and I restarted my machine.
    Result: it's hang!!
    Another option?

    ReplyDelete
  9. This sounds something strange. Could you please check if siebel.D_U_M_M_Y table exists? if yes, please try dropping this table and recheck.

    ReplyDelete
  10. There are some SR opened on the same issue as well.Mentioning about DUMMY table as GG talked about.
    They also mentioned for one work around which is selecting new column and then try Apply.

    Recently because of Project Change..I got a new RDC. In that I have not faced the issue.

    ReplyDelete
  11. Thanks Ankit !! But I tried the suggestion mentioned by Moshi above, and it really worked for me !!! Still not able to find the exact root cause behind this, but seems behaviour of this issue varies from system to system.....

    ReplyDelete
  12. Here is one more update for this post. Today one of my friend (Atul) faced the similar issue and he told me that let the Siebel Tools hanged and wait untill the task get completed. He waited for around 1.5 hour and Siebel Tools came back and Table changes was there in local database. I really got surprised with his observation, anyways this is for your FYI....

    ReplyDelete
  13. Today, Amol has shared very important observation and I believe we have found the root cause of Tools hanging problem during Apply/Activate.

    Reason:
    Siebel Tools get hang in case you have changed the Local database password when you might have recieved the prompt for changing it because of getting expired. So what happens is when you change the password it only get changed for the UserId which you use to login into Siebel Local Tools and it doesn't get reflect to the "Siebel" (Table Owner). "Siebel" userid still tied with the old password. And once you do an apply/activate into Local tools and not able to authenticate it properly, resulted in hang.

    Instead of getting hang, it should have get out of the authentication process immediately and should inform that the "UserId/Password is not correct" but this is not the case here.

    I tested it very well and when I tried apply activate with the old password, it worked fine for me.

    If you want to validate this, just connect to local database (make sure you haven't changed the local database password since you have extracted it) using "dbisqlc.exe" and change the password by using the below SQL:

    ----------------------------------------------
    GRANT CONNECT TO GAURAV IDENTIFIED BY SIEBELMANTRA#1
    -----------------------------------------------

    Please note that in the above SQL,
    UserName = GAURAV (should be in CAPS)
    New Password = SIEBELMANTRA#1 (should be in CAPS)

    and now try apply/activate into Local Database, it should get hang.

    Now kill Siebel Tools from Task Manager and again do a apply/activate using the old password, it will work.

    Give it a try.

    A Big Thanks to Amol, keep up the good work.

    ReplyDelete
    Replies
    1. There was no password change as such. I am still facing this issue.
      Can you please help?

      Delete
  14. Hi GG,

    When I extended the column and tried DDL/Apply tools got hanged but I noticed when I closed the running Siebel Application DDL/Apply finished quickly.

    Cheers,
    Narayan

    ReplyDelete
  15. I was also facing the same problem. But by killing the dbeng.exe from task manager was serve my purpose and I was able to make changes successfully.

    Thanks,
    Anoop

    ReplyDelete
  16. Go to Data Source (ODBC) and do a test connection for your local DB System DSN. Mine was blocked by windows firewall. Once I changed the settings it started working fine.

    ReplyDelete
  17. hi,
    There can be 2 reasons for this:
    1. You are getting error at the very Start of Apply/DDL
    Reason: This is bcoz siebel creates a siebel.D_U_M_M_Y table for applying the changes and w/o proper permissions it will fail in midway
    Soln: Drop the previously created siebel.D_U_M_M_Y table, also always use SIEBEL table to apply these chagnes

    2. If you Tools is getting hanged at the very last step, while applying Table changes:
    Reason: You might be running dbisql.exe or local client, and they lock the DB and hence new table change will nto be apply
    Soln: close any running dbisql.exe and client before Apply/DDL on a table via Tools

    --NAVIN

    ReplyDelete
  18. HI,

    I am also facing this issue, but not in my local I am doing it in server.
    When I click on Apply/DDL on a table via Tools it shows the process fo rsome time and then it gets hanged.

    Thanks

    ReplyDelete
  19. Hi,

    I faced the similar issue and tried all the above said approaches like Virtual memory, killing the dbeg.exe and checking DUMMY table but I did not work for me. At last through the dbisqlc I altered the table and my issue was resolved. So i would recomend that we try this as a last resort.

    ReplyDelete