trying to load POSTGRES table with data from C-ISAM table

ep_datastage · Post by **ep_datastage** » Mon Jan 23, 2017 5:33 pm

Hi, I am trying to load a POSTGRES table with data from a C-ISAM table that contains C# characters.

I am able to extract the data from the C-ISAM tables just fine, but the load to POSTGRES fails due to these characters.

Any ideas ?

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jan 23, 2017 7:35 pm

What characters?

Do you have NLS enabled? If yes, what character set map are you using for the connection to POSTGRES?

ep_datastage · Post by **ep_datastage** » Tue Jan 24, 2017 9:54 am

yes NLS is enabled, set at ms1252-cs , additionally I have UTF-8 set on the C-ISAM and POSTGRES sides. The characters are C# characters added by C# programs that run against the data on C-ISAM.

chulett · Post by **chulett** » Tue Jan 24, 2017 10:08 am

"C# characters" doesn't tell us a whole lot. Are they extended ASCII? What are the actual hex/dec values of the characters?

ep_datastage · Post by **ep_datastage** » Tue Jan 24, 2017 12:47 pm

not exactly sure. They are being called control characters that are written by C# code. I am able to extract the data using UTF-8 from C-ISAM. Just unable to get that same data to load into POSTGRES, which is also set as UTF-8. in checking with the developer on the C-ISAM databaes, his response for these characters was "These control characters are specific to C language, which can be C-Short, C-long etc"

Does that help ? Any suggestions... thanks

chulett · Post by **chulett** » Tue Jan 24, 2017 5:33 pm

Hmmm... it would be best if you could find a way to get a hex or octal dump of the file so we (or you) can determine the values at play here. Clarify this for me - are you looking for a way to load them into your target as-is or are you looking for a way to strip them from the data before you load it?

ep_datastage · Post by **ep_datastage** » Tue Jan 24, 2017 5:53 pm

They have to be loaded AS-IS

chulett · Post by **chulett** » Tue Jan 24, 2017 6:31 pm

Okay, fair enough... can you post the actual failure error(s)? Unedited, please.

ep_datastage · Post by **ep_datastage** » Tue Jan 24, 2017 7:08 pm

yes this is the error

Server:CSDPYETL01
Project:dev_rick
Job No:228
Job name:IB_J0_IPS_POSTGRES_ALL
Invocation:
Event Number:3692
Event type:Info
User:dsadm
Timestamp:1/24/2017 7:22:34 AM
Message Id:IIS-CONN-JDBC-30004
Message:
JDBC_Connector_10,1: The connector encountered a Java exception:
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO rsm_denorm.t0000prtd01 (client_no, client_type, license_no, star_cust_no, pool_no, group_no, mpi_id_no, prod_comp, prod_name, prod_loc, product_type, product_class, pre_prod_fr_date, pre_prod_to_date, prin_from_date, prin_to_date, post_prod_fr_date, post_prod_to_date, cert_of_ins, escalation_flag, sub_type, hfee_override, effective_week_day, direct_deposit_flag, ach_service_co_no, auto_pay_flag, rebate_id, corp_id, pool_flag, pymnt_terms, union_affiliation_001, union_affiliation_002, union_affiliation_003, union_affiliation_004, union_affiliation_005, union_affiliation_006, union_affiliation_007, union_affiliation_008, union_affiliation_009, union_affiliation_010, union_affiliation_011, union_affiliation_012, union_affiliation_013, union_affiliation_014, union_affiliation_015, union_affiliation_016, union_affiliation_017, union_affiliation_018, union_affiliation_019, union_affiliation_020, union_affiliation_021, union_affiliation_022, union_affiliation_023, union_affiliation_024, union_affiliation_025, union_affiliation_026, is_tax_incentive, tax_incentive_locs_001, tax_incentive_locs_002, tax_incentive_locs_003, tax_incentive_locs_004, tax_incentive_locs_005, tax_incentive_locs_006, tax_incentive_locs_007, tax_incentive_locs_008, tax_incentive_locs_009, tax_incentive_locs_010, corp_tax_lock_001, corp_tax_lock_002, corp_tax_lock_003, corp_tax_lock_004, corp_tax_lock_005, corp_tax_lock_006, corp_tax_lock_007, corp_tax_lock_008, corp_tax_lock_009, corp_tax_lock_010, corp_tax_lock_011, corp_tax_lock_012, corp_tax_lock_013, fid_co_no_001, fid_co_no_002, fid_co_no_003, fid_co_no_004, fid_co_no_005, fid_co_no_006, fid_co_no_007, fid_co_no_008, fid_co_no_009, fid_co_no_010, fid_co_no_011, fid_co_no_012, fid_co_no_013, fid_co_no_014, fid_co_no_015, fid_co_no_016, fid_co_no_017, fid_co_no_018, fid_co_no_019, fid_co_no_020, fid_co_no_021, fid_co_no_022, fid_co_no_023, fid_co_no_024, fid_co_no_025, fid_co_no_026, filler_i4_001, filler_i4_002, filler_i4_003, filler_i4_004, filler_i4_005, filler_i4_006, filler_i4_007, filler_i4_008, filler_i4_009, filler_i4_010, filler_i4_011, filler_i4_012, filler_i4_013, filler_i4_014, filler_i4_015, filler_i4_016, filler_i4_017, filler_i4_018, filler_i4_019, filler_i4_020, filler_i4_021, filler_i4_022, filler_i4_023, filler_i4_024, handl_fee_type_001, handl_fee_type_002, handl_fee_type_003, handl_fee_type_004, handl_fee_type_005, handl_fee_type_006, handl_fee_type_007, handl_fee_type_008, handl_fee_type_009, handl_fee_type_010, handl_fee_type_011, handl_fee_type_012, handl_fee_type_013, handl_fee_type_014, handl_fee_type_015, handl_fee_type_016, handl_fee_type_017, handl_fee_type_018, handl_fee_type_019, handl_fee_type_020, handl_fee_type_021, handl_fee_type_022, handl_fee_type_023, handl_fee_type_024, handl_fee_type_025, handl_fee_type_026, major1_phone_no, filler2_001, filler2_002, filler2_003, filler2_004, filler2_005, filler2_006, filler2_007, filler2_008, filler2_009, filler2_010, filler2_011, is_non_union_med, non_union_med_date, handl_fee_amt_001, handl_fee_amt_002, handl_fee_amt_003, handl_fee_amt_004, handl_fee_amt_005, handl_fee_amt_006, handl_fee_amt_007, handl_fee_amt_008, handl_fee_amt_009, handl_fee_amt_010, handl_fee_amt_011, handl_fee_amt_012, handl_fee_amt_013, handl_fee_amt_014, handl_fee_amt_015, handl_fee_amt_016, handl_fee_amt_017, handl_fee_amt_018, handl_fee_amt_019, handl_fee_amt_020, handl_fee_amt_021, handl_fee_amt_022, handl_fee_amt_023, handl_fee_amt_024, handl_fee_amt_025, handl_fee_amt_026, fringe_fee_001, fringe_fee_002, fringe_fee_003, fringe_fee_004, fringe_fee_005, fringe_fee_006, fringe_fee_007, fringe_fee_008, fringe_fee_009, fringe_fee_010, casting_fee_001, casting_fee_002, casting_fee_003, casting_fee_004, casting_fee_005, casting_fee_006, casting_fee_007, casting_fee_008, casting_fee_009, casting_fee_010, filler_f8_001, filler_f8_002, filler_f8_003, filler_f8_004, handl_fee_lim_001, handl_fee_lim_002, handl_fee_lim_003, handl_fee_lim_004, handl_fee_lim_005, handl_fee_lim_006, handl_fee_lim_007, handl_fee_lim_008, handl_fee_lim_009, handl_fee_lim_010, handl_fee_lim_011, handl_fee_lim_012, handl_fee_lim_013, handl_fee_lim_014, handl_fee_lim_015, handl_fee_lim_016, handl_fee_lim_017, handl_fee_lim_018, handl_fee_lim_019, handl_fee_lim_020, handl_fee_lim_021, handl_fee_lim_022, handl_fee_lim_023, handl_fee_lim_024, handl_fee_lim_025, handl_fee_lim_026, BRN_states_001, BRN_states_002, BRN_states_003, BRN_states_004, BRN_states_005, BRN_states_006, filler4, business_registry_no_001, business_registry_no_002, business_registry_no_003, business_registry_no_004, business_registry_no_005, business_registry_no_006, filler5, bill_type_001, bill_type_002, bill_type_003, bill_type_004, pay_type_001, pay_type_002, pay_type_003, pay_type_004, pay_amt_001, pay_amt_002, pay_amt_003, pay_amt_004, descriptor_001, descriptor_002, descriptor_003, descriptor_004, signator_name_001, signator_name_002, signator_name_003, signator_name_004, signator_name_005, signator_name_006, signator_name_007, signator_name_008, signator_name_009, signator_name_010, local_id_no_001, local_id_no_002, local_id_no_003, local_id_no_004, local_id_no_005, local_id_no_006, local_id_no_007, local_id_no_008, local_id_no_009, local_id_no_010, tape_flag_001, tape_flag_002, tape_flag_003, tape_flag_004, tape_flag_005, tape_flag_006, tape_flag_007, tape_flag_008, tape_flag_009, tape_flag_010, prod_no, deposit_value, aff_status_001, aff_status_002, aff_status_003, aff_status_004, aff_status_005, aff_status_006, aff_status_007, aff_status_008, aff_status_009, aff_status_010, aff_status_011, aff_status_012, aff_status_013, aff_status_014, aff_status_015, aff_status_016, aff_status_017, aff_status_018, aff_status_019, aff_status_020, aff_status_021, aff_status_022, aff_status_023, aff_status_024, aff_status_025, aff_status_026, filler3_001, filler3_002, filler3_003, filler3_004, filler3_005, filler3_006, filler3_007, filler3_008, filler3_009, filler3_010, filler3_011, filler3_012, filler3_013, filler3_014, filler3_015, filler3_016, filler3_017, filler3_018, filler3_019, filler3_020, filler3_021, filler3_022, filler3_023, filler3_024, client_status, no_of_episodes, minors, gl_coding, contract_type, tv_type, client_system, transfer_medium, media_type, modem_phone_no_1, modem_phone_no_2, start_time, end_time, no_of_retries, backup, in_computer_time, out_computer_time, incoming_xfer, contact_name, contact_number, vac_accr_flag, hol_accr_flag, lhf_accr_flag, system_delivery_001, system_delivery_002, system_delivery_003, system_delivery_004, system_contract_rec_001, system_contract_rec_002, system_contract_rec_003, system_contract_rec_004, ups_flag, invoice_level, credit_po_flag, monitor, installation, future_interface, required_date, invoice_limit, corp_limit, ind_limit, check_limit, holding_fee_001, holding_fee_002, holding_fee_003, est_inv_copies, inv_mail_instr, vista_version, delivery_date, vendor_template_yn, vendor_template, invoiced_weekly_flag, cast_ny_print, gv_license_no, load_cofa, dos, modem, network, system, module, service, ram_size, drive_size, del_mode, report_headings, spf_rebate_flag, remote_pyrl_print, electronic_setup, hr_email, market_ep_rep, payroll_model, sub_group, letr_of_guar_gr_override, no_of_users, windows, rate_1, rate_2, variance_on_rpts, printer, description_1, description_2, state, delivery_phone, terms_of_agreement, hf_type_1, hf_type_2, occ_code_1, mandays, hardware, system_comment, file_no, occ_code_2, asum_agmt, percentage, na_coord_flag, local717_flag, contract_no, contract_date, pyrl_rev_date, major1_name, major1_addr1, major1_addr2, major1_city, major1_state, major1_zip, major1_country, major2_name, major2_addr1, major2_addr2, major2_city, major2_state, major2_zip, major2_country, studio_name, studio_phone, studio_fax_no, studio_email, payroll_name, payroll_phone, payroll_fax_no, payroll_email, account_name, account_phone, account_fax_no, account_email, upm_name, upm_phone, upm_fax_no, upm_email, ar_name, ar_phone, ar_fax_no, ar_email, pr_tax_adm_name, pr_tax_adm_phone, pr_tax_adm_fax_no, pr_tax_adm_email, market_name, market_phone, market_fax_no, market_email, labor_comment, payroll_comment, prod_shoot_len, comm_aicp, rsid_setup_fee, musc_assmp_agrmt, musc_ind_ltr, musc_nonind_ltr, contract_rec, contract_addr1, contract_addr2, contract_city, contract_state, contract_zip, ins_co, ins_contact, ins_phone, ins_fax, studio_email_2, payroll_email_2, account_email_2, business_model, postage_flag, supplimental_fui_flag, tt_system, tt_license_no, prod_lics_req_date, use_report_image, commercial_type, is_pool_group_flag, ss_system, ss_license_no, cc_system, cc_license_no, do_duplicate_tc_check, ccntry_forgn_cntry, carlt_ar_long_trm_001, carlt_ar_long_trm_002, carlt_ar_long_trm_003, carlt_ar_long_trm_004, carlt_ar_long_trm_005, carlt_ar_long_trm_006, carlt_ar_long_trm_007, carlt_ar_long_trm_008, cdefin_defferd_inc_001, cdefin_defferd_inc_002, cdefin_defferd_inc_003, cdefin_defferd_inc_004, cdefin_defferd_inc_005, cdefin_defferd_inc_006, cdefin_defferd_inc_007, cdefin_defferd_inc_008, cprofl_promo_flg, cinvfl_invoice_flg, ctitfl_ttl_tap_flg, cindfl_indep_flg, cmarkt_market_name, ins_exp_date, mpip_pix, local_600_signator_name, local_600_id_no, local_600_tape_flag, local_600_mpip_pix, ep_products_001, ep_products_002, ep_products_003, ep_products_004, ep_products_005, ep_products_006, ep_products_007, ep_products_008, ep_products_009, ep_products_010, wkc_coverage, wkc_eff_date, wkc_policy_start, wkc_policy_end, wkc_coverage_extn, wkc_num_of_days, pay_method, deposit_require, country, cn_province, vpo_rpt_pkg_flag, season_id, hr_name, hr_phone, hr_fax_no, user_id, ent_date, ent_time, cnxrownumber) VALUES (2, 'PP', ' ', '1200000000', 2, 1, ' ', 'VISTA 2000 ', 'VISTA 2000 ', ' ', 1, 4, '20000101', ' ', '20000101', ' ', ' ', '20001231', ' ', ' ', 'A ', ' ', ' ', ' ', ' ', ' ', ' ', '602820 ', 'N ', 'CE', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, '

And where the values stop is the first column with the Control characters from C#

Thanks

chulett · Post by **chulett** » Wed Jan 25, 2017 6:43 am

FYI since you are new here. There's no need to quote everything all the time, even if the 'Reply with quote' is very conveniently located right over <points up and to the right> there. Please use the Reply to topic link that's at the top and bottom of every page, that will save me the cleanup effort.

Dang, that error doesn't help nearly as much as I hoped it might. It doesn't even show us the 'bad' characters. And I for one have no idea what a "java sql BatchUpdateException" would be. Have you involved your official support provider yet? That would be prudent. And I'm still wondering if you can get (perhaps with help) to find out the actual problematic hex values of these control characters.

I understand you need to load the data AS-IS into POSTGRES but don't see why the C-ISAM control characters (which is not data, btw) would need to be part of the package. Are you certain that is part of your requirements? They aren't going to mean diddly to your database table or to whomever is reading it, I would wager.

chulett · Post by **chulett** » Wed Jan 25, 2017 10:06 am

Okay... random thought on the drive in to work... HOW are you reading / getting the data from the C-ISAM "table"? From what I recall, those are represented by two files - one .dat and one .idx that make up what can be treated as a table. Are you reading the .dat file directly somehow? Or has someone dumped the contents to a flat file for you? The latter should not be an issue, the former... would. Or at least definitely could be. Details, please.

ep_datastage · Post by **ep_datastage** » Wed Jan 25, 2017 1:11 pm

I am using a CONNX URL to read the data from the C-ISAM tables. And the purpose is to have the group that is using C-ISAM to move from C-ISAM to POSTGRES and still use the same program code to access the data. Therefore the Control Characters have to remain in the data.

how do i attach a screen shot

ep_datastage · Post by **ep_datastage** » Wed Jan 25, 2017 1:16 pm

Okay, I figured out how to get at least one of the values to display in hex

here is what is displaying in one of the columns

BRN_states_001
--------------
efbfbdefbfbd

Thanks

chulett · Post by **chulett** » Wed Jan 25, 2017 1:44 pm

ep_datastage wrote:how do i attach a screen shot

You upload it to one of the plethora of free file hosting sites (one example, http://imgur.com/) and then use URL tags to get it added to your post. They will automatically generate them for you. Or worst case just post the URL and people can click on it.

ep_datastage · Post by **ep_datastage** » Wed Jan 25, 2017 2:48 pm

Actually, I posted the values to the site already,,,

here is what is in the column in hex

BRN_states_001
--------------
efbfbdefbfbd