# I need a very important help - need to read a DB2 database **URL:** \ **Category:** Logstash\ **Created:** [December 20, 2018, 11:47am UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691 "2018-12-20T11:47:02Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![angelogabeira](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@angelogabeira](https://discuss.elastic.co/u/angelogabeira)\ **Post date:** [December 20, 2018, 11:47am UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/1 "2018-12-20T11:47:02Z")
Need to read a DB2 database I'm using this code: ## input { jdbc { jdbc\_connection\_string =\> "jdbc:db2://xxxxx.yyyyy.com.br:50100/DB2XXX" jdbc\_user =\> "DB2YYY" jdbc\_password =\> "010101" jdbc\_driver\_library =\> "/home/analytics/Documentos/db2jcc.jar,/home/analytics/Documentos/db2jcc\_license\_cu.jar,/home/analytics/Documentos/db2jcc4.jar" jdbc\_driver\_class =\> "com.ibm.db2.jcc.DB2Driver" jdbc\_fetch\_size =\> 1000 schedule =\> "\* \* \* \* \*" statement =\> "SELECT \* FROM DB2BBR.MNFC\_PTL" } } output { elasticsearch { index =\> "DB2-BBR" document\_id =\> "%{pkkey}" hosts =\> [localhost:9200] } stdout { codec =\> rubydebug } } I'm getting this feedback analytics@ES74191324389507129:/usr/share/logstash/bin$ sudo ./logstash -f /home/analytics/Documentos/logstash\_conectDB2.conf --path.settings /etc/logstash Sending Logstash logs to /var/log/logstash which is now configured via log4j2.properties [2018-12-20T09:28:13,396][WARN][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified [2018-12-20T09:28:13,504][INFO][logstash.runner] Starting Logstash {"logstash.version"=\>"6.5.3"} [2018-12-20T09:28:14,774][ERROR][logstash.agent] Failed to execute action {:action=\>LogStash::PipelineAction::Create/pipeline\_id:main, :exception=\>"LogStash::ConfigurationError", :message=\>"Expected one of #, {, ,, ] at line 19, column 24 (byte 630) after output {\r\n elasticsearch { index =\> "DB2-BBR"\r\n document\_id =\> "%{pkkey}"\r\n\t\t hosts =\> [localhost", :backtrace=\>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in`compile\_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2486:in`map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:in`initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:22:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:90:in`initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline\_action/create.rb:42:in `block in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:in`block in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in `synchronize'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:in`exclusive'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline\_action/create.rb:38:in `execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:317:in`block in converge\_state'"]} [2018-12-20T09:28:15,166][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600} ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 12:08pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/2 "2018-12-20T12:08:10Z")
Hello, try this instead of using statement =\> "SELECT \* FROM DB2BBR.MNFC\_PTL" use like statement =\> "SELECT id,name,.... FROM DB2BBR.MNFC\_PTL" ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 12:13pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/3 "2018-12-20T12:13:49Z")
Make sure you have the file, .logstash\_jdbc\_last\_run, it will be present in your machine orelse, create an empty folder input { jdbc { jdbc\_driver\_library =\> "XXXXX/DB.jdbc\_5.1.5.jar" jdbc\_driver\_class =\> "XXXXXX" jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/testdb" jdbc\_user =\> "root" jdbc\_password =\> "root" statement =\> "SELECT id,name FROM sample where id \> :sql\_last\_value" tracking\_column =\> "id" use\_column\_value =\> true last\_run\_metadata\_path =\> "C:/Users/XXXXX/.logstash\_jdbc\_last\_run" schedule =\> "\*/1 \* \* \* \*" } } output { elasticsearch { hosts =\> ["localhost:9200"] index =\> "idx\_mysqldata" } stdout { codec =\> rubydebug } } ---
**Author:** ![angelogabeira](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@angelogabeira](https://discuss.elastic.co/u/angelogabeira)\ **Post date:** [December 20, 2018, 1:16pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/4 "2018-12-20T13:16:38Z")
Thanks for your willingness to help. I did not find the "file" .logstash\_jdbc\_last\_run Should I install? should I create? if it is to create, what should be the content? I'm still standing, that's very important to me. Thank you ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 2:26pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/5 "2018-12-20T14:26:06Z")
just create empty folder with the name ".logstash\_jdbc\_last\_run" and use it. ---
**Author:** ![angelogabeira](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@angelogabeira](https://discuss.elastic.co/u/angelogabeira)\ **Post date:** [December 20, 2018, 2:44pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/6 "2018-12-20T14:44:51Z")
Where should I create the folder? ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 20, 2018, 2:45pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/7 "2018-12-20T14:45:45Z")
anywhere in your machine and give the path in your CONF file where you have kept the file ---
**Author:** ![angelogabeira](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@angelogabeira](https://discuss.elastic.co/u/angelogabeira)\ **Post date:** [December 20, 2018, 3:47pm UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/8 "2018-12-20T15:47:43Z")
I did so ``` jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver" jdbc_fetch_size => 1000 last_run_metadata_path => "/usr/share/logstash/logstash_jdbc_last_run" schedule => "* * * * *" statement => "SELECT * FROM DB2BBR.MNFC_PTL" ``` * * * gave this return analytics@ES74191324389507129:/usr/share/logstash/bin$ sudo ./logstash -f /home/analytics/Documentos/logstash\_conectDB2.conf --path.settings /etc/logstash Sending Logstash logs to /var/log/logstash which is now configured via log4j2.properties [2018-12-20T13:43:52,146][WARN][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified [2018-12-20T13:43:52,164][INFO][logstash.runner] Starting Logstash {"logstash.version"=\>"6.5.3"} [2018-12-20T13:43:53,456][ERROR][logstash.agent] Failed to execute action {:action=\>LogStash::PipelineAction::Create/pipeline\_id:main, :exception=\>"LogStash::ConfigurationError", :message=\>"Expected one of #, {, } at line 16, column 37 (byte 634) after output {\r\n elasticsearch { hosts =\> localhost", :backtrace=\>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in`compile\_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2486:in`map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:in`initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:22:in `initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:90:in`initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline\_action/create.rb:42:in `block in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:in`block in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in `synchronize'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:in`exclusive'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline\_action/create.rb:38:in `execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:317:in`block in converge\_state'"]} [2018-12-20T13:43:53,801][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600} ---
**Author:** ![angelogabeira](https://avatars.discourse-cdn.com/v4/letter/a/ec9cab/32.png) [@angelogabeira](https://discuss.elastic.co/u/angelogabeira)\ **Post date:** [December 21, 2018, 11:22am UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/9 "2018-12-21T11:22:48Z")
I managed to walk some more, but now the mistake is another. Does anyone have any ideas to help? # ============================================== analytics@ES74191324389507129:/usr/share/logstash/bin$ sudo ./logstash -f /home/analytics/Documentos/logstash\_conectDB2.conf --path.settings /etc/logstash Sending Logstash logs to /var/log/logstash which is now configured via log4j2.properties [2018-12-21T09:16:00,732][WARN][logstash.config.source.multilocal] Ignoring the 'pipelines.yml' file because modules or command line options are specified [2018-12-21T09:16:00,748][INFO][logstash.runner] Starting Logstash {"logstash.version"=\>"6.5.3"} [2018-12-21T09:16:04,714][INFO][logstash.pipeline] Starting pipeline {:pipeline\_id=\>"main", "pipeline.workers"=\>4, "pipeline.batch.size"=\>125, "pipeline.batch.delay"=\>50} [2018-12-21T09:16:05,385][INFO][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=\>{:removed=\>, :added=\>[[http://localhost:9200/](http://localhost:9200/)]}} [2018-12-21T09:16:05,631][WARN][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=\>"[http://localhost:9200/](http://localhost:9200/)"} [2018-12-21T09:16:05,770][INFO][logstash.outputs.elasticsearch] ES Output version determined {:es\_version=\>6} [2018-12-21T09:16:05,773][WARN][logstash.outputs.elasticsearch] Detected a 6.x and above cluster: the `type` event field won't be used to determine the document \_type {:es\_version=\>6} [2018-12-21T09:16:05,798][INFO][logstash.outputs.elasticsearch] New Elasticsearch output {:class=\>"LogStash::Outputs::ElasticSearch", :hosts=\>["[//localhost:9200](https://localhost:9200)"]} [2018-12-21T09:16:05,823][INFO][logstash.outputs.elasticsearch] Using mapping template from {:path=\>nil} [2018-12-21T09:16:05,876][INFO][logstash.outputs.elasticsearch] Attempting to install template {:manage\_template=\>{"template"=\>"logstash-_", "version"=\>60001, "settings"=\>{"index.refresh\_interval"=\>"5s"}, "mappings"=\>{"default"=\>{"dynamic\_templates"=\>[{"message\_field"=\>{"path\_match"=\>"message", "match\_mapping\_type"=\>"string", "mapping"=\>{"type"=\>"text", "norms"=\>false}}}, {"string\_fields"=\>{"match"=\>"_", "match\_mapping\_type"=\>"string", "mapping"=\>{"type"=\>"text", "norms"=\>false, "fields"=\>{"keyword"=\>{"type"=\>"keyword", "ignore\_above"=\>256}}}}}], "properties"=\>{"@timestamp"=\>{"type"=\>"date"}, "@version"=\>{"type"=\>"keyword"}, "geoip"=\>{"dynamic"=\>true, "properties"=\>{"ip"=\>{"type"=\>"ip"}, "location"=\>{"type"=\>"geo\_point"}, "latitude"=\>{"type"=\>"half\_float"}, "longitude"=\>{"type"=\>"half\_float"}}}}}}}} [2018-12-21T09:16:05,981][INFO][logstash.outputs.elasticsearch] Installing elasticsearch template to \_template/logstash [2018-12-21T09:16:06,454][ERROR][logstash.pipeline] Error registering plugin {:pipeline\_id=\>"main", :plugin=\>"\"DB2I147C", last\_run\_metadata\_path=\>"/usr/share/logstash/logstash\_jdbc\_last\_run", schedule=\>"\* \* \* \* \*", jdbc\_password=\>, statement=\>" SELECT \* from DB2BBR.CD\_UOR\_RSP\_SLC ", jdbc\_fetch\_size=\>1000, jdbc\_driver\_library=\>"/home/analytics/Documentos/db2jcc.jar,/home/analytics/Documentos/db2jcc\_license\_cu.jar,/home/analytics/Documentos/db2jcc4.jar", jdbc\_connection\_string=\>"jdbc:db2://gwdb2.bb.com.br:50100/BDB2P04", id=\>"243a6019d5772ac44be1ff03cac2357825e26dd4e9f14cd208ced50573431f0f", jdbc\_driver\_class=\>"com.ibm.db2.jcc.DB2Driver", enable\_metric=\>true, codec=\>\"plain\_c8e4108a-d81f-4a13-8eb7-a2027a0748ad", enable\_metric=\>true, charset=\>"UTF-8"\>, jdbc\_paging\_enabled=\>false, jdbc\_page\_size=\>100000, jdbc\_validate\_connection=\>false, jdbc\_validation\_timeout=\>3600, jdbc\_pool\_timeout=\>5, sql\_log\_level=\>"info", connection\_retry\_attempts=\>1, connection\_retry\_attempts\_wait\_time=\>0.5, use\_column\_value=\>false, tracking\_column\_type=\>"numeric", clean\_run=\>false, record\_last\_run=\>true, lowercase\_column\_names=\>true\>", :error=\>"Is a directory - /usr/share/logstash/logstash\_jdbc\_last\_run", :thread=\>"#\"} [2018-12-21T09:16:06,804][ERROR][logstash.pipeline] Pipeline aborted due to error {:pipeline\_id=\>"main", :exception=\>#\, :backtrace=\>["org/jruby/RubyIO.java:3600:in `read'", "/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/value_tracking.rb:106:in`read'", "/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin\_mixins/jdbc/value\_tracking.rb:80:in `get_initial'", "/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/value_tracking.rb:36:in`initialize'", "/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin\_mixins/jdbc/value\_tracking.rb:29:in `build_last_value_tracker'", "/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:216:in`register'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:242:in `register_plugin'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:253:in`block in register\_plugins'", "org/jruby/RubyArray.java:1734:in `each'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:253:in`register\_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:396:in `start_inputs'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:294:in`start\_workers'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:200:in `run'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:160:in`block in start'"], :thread=\>"#\"} [2018-12-21T09:16:06,822][ERROR][logstash.agent] Failed to execute action {:id=\>:main, :action\_type=\>LogStash::ConvergeResult::FailedAction, :message=\>"Could not execute action: PipelineAction::Create, action\_result: false", :backtrace=\>nil} [2018-12-21T09:16:07,080][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=\>9600} This is the conf I am using input { jdbc { jdbc\_connection\_string =\> "jdbc:db2://xxxx.yyy.com.br:50100/BDB2P04" jdbc\_user =\> "DBYYYY" jdbc\_password =\> "XXXXXX" jdbc\_driver\_library =\> "/home/analytics/Documentos/db2jcc.jar,/home/analytics/Documentos/db2jcc\_license\_cu.jar,/home/analytics/Documentos/db2jcc4.jar" jdbc\_driver\_class =\> "com.ibm.db2.jcc.DB2Driver" jdbc\_fetch\_size =\> 1000 last\_run\_metadata\_path =\> "/usr/share/logstash/logstash\_jdbc\_last\_run" schedule =\> "\* \* \* \* \*" statement =\> " SELECT \* from DB2BBR.CD\_UOR\_RSP\_SLC " } } output { elasticsearch { hosts =\> ["localhost:9200"] index =\> "DB2-BBR" document\_id =\> "%{pkkey}" } stdout { codec =\> rubydebug } } ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 18, 2019, 11:23am UTC](https://discuss.elastic.co/t/i-need-a-very-important-help-need-to-read-a-db2-database/161691/10 "2019-01-18T11:23:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.