SAP exchange space(msn:erp.expert@hotmail.com)

sessionid in genero fourjs fjs tiptop gp 5.2:

# Prog. Version..: ‘5.20.01’
#
# Program name…: cl_insert_sid.4gl
# Descriptions…: 將view所需要的資訊新增到sid_file內
# Date & Author..: 2009/07/24 by Hiko
# Usage……….: CALL cl_ins_del_sid(1)
# Modify………: No.FUN-980030 09/08/10 by Hiko:For GP5.2
DATABASE ds
GLOBALS “../../config/top.global”
##################################################
# Descriptions…: 將view所需要的資訊新增到sid_file內
# Date & Author..: 2009/07/24 by Hiko   #FUN-980030
# Input Parameter: p_action 1:新增 2:刪除
#                : p_plant 傳入工廠(跨工廠時,才可抓到正確的資料)
# Return code….: void
##################################################
FUNCTION cl_ins_del_sid(p_action, p_plant)
   DEFINE p_action  SMALLINT,
          p_plant   LIKE azw_file.azw01
   DEFINE l_sid01   LIKE sid_file.sid01
   DEFINE l_pid     LIKE type_file.num10
   DEFINE l_sid_cnt SMALLINT
   DEFINE l_dbs     LIKE azw_file.azw06
   #有調整時, 要記得與cl_user,cl_used同步.
   IF cl_null(p_plant) THEN
      LET p_plant = g_plant
   END IF
   IF p_action=1 THEN
      CASE cl_db_get_database_type()
         WHEN “ORA”
            SELECT USERENV(‘SESSIONID’) INTO l_sid01 FROM DUAL
         WHEN “MSV”
            PREPARE s1 FROM “SELECT @@SPID “
            EXECUTE s1 INTO l_sid01
         WHEN “DB2”
            SELECT application_id INTO l_sid01 FROM sysibm.sysdummy1
         OTHERWISE
            DISPLAY “Error: TIPTOP Still NOT Support “,cl_db_get_database_type(),” Database.”
            EXIT PROGRAM
      END CASE

      SELECT count(*) INTO l_sid_cnt FROM sid_file WHERE sid01=l_sid01
      IF SQLCA.SQLCODE THEN
         CALL cl_err(‘sid_file error:’,SQLCA.SQLCODE,1)
         RETURN
      END IF

      IF l_sid_cnt > 0 THEN
         #刪除原本就存在相同的SESSIONID,這樣可以避免重覆.
         DELETE FROM sid_file WHERE sid01=l_sid01
      END IF
      IF p_plant = g_plant THEN
         LET l_dbs = g_dbs
      ELSE
         SELECT azw06 INTO l_dbs FROM azw_file WHERE azw01=p_plant
      END IF
      LET l_pid = FGL_GETPID()
      LET g_today = TODAY
      LET g_time = TIME
      DISPLAY “”
      DISPLAY “SESSIONID:”,l_sid01,”  PLANT:”,p_plant,”  DBNAME:”,l_dbs
      INSERT INTO sid_file VALUES(l_sid01,p_plant,l_dbs,g_user,l_pid,g_prog,g_time,g_today)
   ELSE
      #DISPLAY “DELETE SESSIONID…”
      CASE cl_db_get_database_type()
         WHEN “ORA”
            SELECT USERENV(‘SESSIONID’) INTO l_sid01 FROM DUAL
         WHEN “MSV”
            PREPARE s2 FROM “SELECT @@SPID “
            EXECUTE s2 INTO l_sid01
         WHEN “DB2”
            SELECT application_id INTO l_sid01 FROM sysibm.sysdummy1
         OTHERWISE
            DISPLAY “Error: TIPTOP Still NOT Support “,cl_db_get_database_type(),” Database.”
            EXIT PROGRAM
      END CASE

      SELECT count(*) INTO l_sid_cnt FROM sid_file WHERE sid01=l_sid01
      IF SQLCA.SQLCODE THEN
         CALL cl_err(‘sid_file error:’,SQLCA.SQLCODE,1)
         RETURN
      END IF
      IF l_sid_cnt > 0 THEN
         #資料存在才刪除.
         DELETE FROM sid_file WHERE sid01=l_sid01
      END IF
   END IF
END FUNCTION

===================================================================================================

— Create table
create table ds.SID_FILE
(
  sid01   NUMBER(20) not null,
  sid02   VARCHAR2(10),
  sid03   VARCHAR2(20),
  sid04   VARCHAR2(10),
  sid05   NUMBER(10),
  sid06   VARCHAR2(20),
  sid07   VARCHAR2(8),
  siddate DATE
)
tablespace DBS1
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 512K
    minextents 1
    maxextents unlimited
  );
— Create/Recreate indexes
create unique index ds.SID_01 on ds.SID_FILE (SID01)
  tablespace DBS1
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 192K
    minextents 1
    maxextents unlimited
  );
— Grant/Revoke object privileges
grant index on ds.SID_FILE to PUBLIC;
grant select, insert, update, delete on ds.SID_FILE to TIPTOPGP;

============================================================================================

Comments: I think that the ‘sid_file’ and ‘read sessionid’ is the reason about the system is running slowly.

technology exchange:

skype:mrsf_jean.luc

msn:erp.expert@hotmail.com