I need a very important help - need to read a DB2 database

Elastic Stack Logstash
1

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:incompile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:ininitialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:22:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:90:ininitialize'", "/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:inblock in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in synchronize'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:inexclusive'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:38:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:317:inblock in converge_state'"]}
[2018-12-20T09:28:15,166][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}

2

Hello, try this

instead of using
statement => "SELECT * FROM DB2BBR.MNFC_PTL"

use like
statement => "SELECT id,name,.... FROM DB2BBR.MNFC_PTL"

3

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 }

}

4

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

5

just create empty folder with the name ".logstash_jdbc_last_run" and use it.

6

Where should I create the folder?

7

anywhere in your machine and give the path in your CONF file where you have kept the file

8

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:incompile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2486:inmap'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:149:ininitialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:22:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:90:ininitialize'", "/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:inblock in exclusive'", "org/jruby/ext/thread/Mutex.java:148:in synchronize'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:92:inexclusive'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:38:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:317:inblock in converge_state'"]}
[2018-12-20T13:43:53,801][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}

9

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/]}}
[2018-12-21T09:16:05,631][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>"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"]}
[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=>"<LogStash::Inputs::Jdbc jdbc_user=>"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=><LogStash::Codecs::Plain id=>"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=>"#<Thread:0x1cf51e0e run>"}
[2018-12-21T09:16:06,804][ERROR][logstash.pipeline ] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<Errno::EISDIR: Is a directory - /usr/share/logstash/logstash_jdbc_last_run>, :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:inread'", "/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:ininitialize'", "/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:inregister'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:242:in register_plugin'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:253:inblock in register_plugins'", "org/jruby/RubyArray.java:1734:in each'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:253:inregister_plugins'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:396:in start_inputs'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:294:instart_workers'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:200:in run'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:160:inblock in start'"], :thread=>"#<Thread:0x1cf51e0e run>"}
[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 }
}

10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.