One Star

SAM server configuration with mysql backend in ESB

Hi  can any one help me to configure mysql database as my datasource with SAM server for ESB.Please give me some detail steps to configure mysql server in SAM server.
6 REPLIES

Re: SAM server configuration with mysql backend in ESB

Need details to connect mysql as Datasource instead of Derby(Default) any quick suggestions are appreciated . Thanks in Advance.
Employee

Re: SAM server configuration with mysql backend in ESB

Hi, 
SAM supports multiple backend DB, e.g MySQL, DB2, Oracle, etc, Derby is the default, if you want to switch to using MySQL Database, please following the steps:
1. install MySQL driver:  install mvn:mysql/mysql-connector-java/5.1.18
2. install the provided MySQL datasource feature:  features:install tesb-datasource-mysql
3. edit properties in the Container/etc/org.talend.esb.datasource.mysql.cfg according to your MySQL env.
HTH
One Star

Re: SAM server configuration with mysql backend in ESB

Hi,
I tried those steps  but whenever am starting  SAM server through karaf, bydefault  derby is running in backend in place of mysql though i placed mysql connector and set the config file with mysql env.
Employee

Re: SAM server configuration with mysql backend in ESB

Hi,
Please stop the default SAM (tesb:stop-sam) before you do the steps I proposed. and additional steps:
4) edit etc/org.talend.esb.sam.server.cfg
    db.datasource=ds-mysql
    db.dialect=mysqlDialect
5) install SAM server by features
    features:install tesb-sam-service-soap tesb-sam-service-rest
One Star

Re: SAM server configuration with mysql backend in ESB

Still am not getting the right solution though i follow the above steps ....
Could anyone give me some more info..to configure SAM server with mysql as backend
Thanks in Advance 
Seven Stars

Re: SAM server configuration with mysql backend in ESB

Hi Prem,
Please follow the following steps in order to configure SAM server. let me know if have any questions or email to me my email id is ambothu.kumar@ossscube.com. By using following steps I successfully configured SAM server in TAC.
Service Activity monitoring : Here, we have to install agent (By default, it come in Talend ESB/Run time installation) and server
     Agent Config file : /opt/Talend-6.1.1/esb/container/etc/org.talend.esb.sam.agent.cfg
Here, we installed SAM in apache-tomcat server. For installing SAM server, we need to copy sam-server-war.war file from /opt/Talend-6.1.1/esb/add-ons/sam/sam-server-war.war to /opt/Talend-6.1.1/tac/apache-tomcat/webapps/ and then restart apache-tomcat server.

URL : http://<domain>:8080/sam-server-war/services/sam  for access SAM server.
https://lh3.googleusercontent.com/MUpq_l6A272O25MEV8BQsFETm7GoEXfdhI9rzchwC2dZ9EgYddheCeN6Te3VtRdNQ4tG8dgqpHFOFWopVdYkCiiaXGJGfHV_Q5WrReBMeXOtGB5ocoTcw4ET3Hktr4IMqUFrR6Fh
Note 1: Before creating sam database we have to install datasource that we want to use for sam in runtime as follows.
i) Run talend runtime i.e. cd <TalendRuntime>/container/bin
ii) ./trun after executing this command it prompts Talend runtime.
iii) Execute following commands at talend runtime.
karaf> bundle:install mvn:mysql/mysql-connector-java/5.1.18(according to your database)

karaf>feature:install tesb-datasource-mysql
iv) Edit vi /opt/Talend-6.1.1/esb/container/etc/org.talend.esb.datasource.mysql.cfg File and data source details as follows.
     datasource.url=jdbc:mysql://localhost:3306/sam
     datasource.username=root
     datasource.password=root
v) Restart Talend runtime.
Note 2: We have to install SAM database to log all the details of service activity monitoring.
         i) We have to create following three tables in sam database .
CREATE TABLE `EVENTS` (
 `ID` bigint(20) NOT NULL,
 `EI_TIMESTAMP` datetime DEFAULT NULL,
 `EI_EVENT_TYPE` varchar(255) DEFAULT NULL,
 `ORIG_CUSTOM_ID` varchar(255) DEFAULT NULL,
 `ORIG_PROCESS_ID` varchar(255) DEFAULT NULL,
 `ORIG_HOSTNAME` varchar(128) DEFAULT NULL,
 `ORIG_IP` varchar(64) DEFAULT NULL,
 `ORIG_PRINCIPAL` varchar(255) DEFAULT NULL,
 `MI_PORT_TYPE` varchar(255) DEFAULT NULL,
 `MI_OPERATION_NAME` varchar(255) DEFAULT NULL,
 `MI_MESSAGE_ID` varchar(255) DEFAULT NULL,
 `MI_FLOW_ID` varchar(64) DEFAULT NULL,
 `MI_TRANSPORT_TYPE` varchar(255) DEFAULT NULL,
 `MESSAGE_CONTENT` longtext,
 `CONTENT_CUT` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `EVENTS_TIMESTAMP` (`EI_TIMESTAMP`),
 KEY `EVENTS_FLOW_TIMESTAMP` (`MI_FLOW_ID`,`EI_TIMESTAMP`),
 KEY `EVENTS_MI_PORT_TYPE` (`MI_PORT_TYPE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `EVENTS_CUSTOMINFO` (
 `ID` bigint(20) NOT NULL,
 `EVENT_ID` bigint(20) NOT NULL,
 `CUST_KEY` varchar(255) DEFAULT NULL,
 `CUST_VALUE` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `EVENTS_CUSTOMINFO_EVENT_ID` (`EVENT_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `SEQUENCE` (
 `SEQ_NAME` varchar(50) NOT NULL,
 `SEQ_COUNT` decimal(38,0) DEFAULT NULL,
 PRIMARY KEY (`SEQ_NAME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
        ii) Open the <TomcatPath>/conf/context.xml file and add the following lines, according to your database server:
<Resource name="jdbc/datasource" auth="Container"
type="javax.sql.DataSource" username="talend"             password="talendpasswd"
       driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://web-talend-mysql-rds.cocqqszlvtdy.us-east-1.rds.amazonaws.com:3306/sam_dev4"
       maxActive="8" maxIdle="30" maxWait="10000"/>
      iii) Change the database connection information(from Derby to MySql) specified in the following file, and edit the following file with the lines mentioned underneath to it.
      <TomcatPath>/webapps/sam-server-war/WEB-INF/logserver.properties
#for Derby (by default)
#db.datasource=java:comp/env/jdbc/datasource
#db.dialect=derbyDialect
#for Mysql
db.datasource=java:comp/env/jdbc/datasource
db.dialect=mysqlDialect
  iv) Restart tac server.
Thanks & Regards
A Ravi Kumar
Mobile Number : +91 852-762-1083
Email-id : a.ravikumar104@gmail.com
Skype Id : ammanannaravikumar