unix,db2,sh , db2 - export into File

db2 - export into File


Tag: unix,db2,sh

I'm trying to export the result of a query in db2 to file, but the result like:


What can I do to obtain the following result:


My current query:

EXPORT TO /tmp/tmp.tmp OF del modified by NOCHARDEL coldel|
max(case when alias = 'abc' then INT_VALUE else null end) as A,
max(case when alias = 'xyz' then STRING_VALUE else null end) as X,
from lsw_bpd_instance_variables iv
where bpd_instance_id in ($instancesDB)
group by iv.bpd_instance_id
with ur


The default behavior of DB2's EXPORT utility is to format any DECIMAL value with a leading positive/negative sign and pad it with leading zeros. To override this behavior, specify the DECPLUSBLANK and STRIPLZEROS options in your MODIFIED BY clause, or CAST the DECIMAL values to some other type in your SELECT statement.


Selecting unique lines based on two columns

I've been trying to figure out a way to select lines that are unique based on the values of two columns. For example, here is a sample/example of my file: chr1 10 12 chr1 10 12 chr1 10 11 chr1 9 12 chr2 15 20 And this is what I...

awk — adding a new delimiter to the default space delimiter

awk default delimiter space treats any amount of space between two fields as equivalent.. echo "1 2"|awk '{for (i=1;i<=NF;i++) print $i}' #which gives the result (two spaces between 1 and 2) 1 2 How can I add "=" to this existing delimiter? I have tried the following and that has...

How can I configure and allow pipes in my bash script?

I'm a beginner writing Bash scripts so I'm confused on the architecture of pipes. For the basic commands such as grep for example, did pipes have to be somehow configured and programmed to be allowed into the command? Or do pipes just automatically pass in the outputs as the first...

Replacing alternate spaces with newline?

I am trying to replace the alternate spaces with newlines using UNIX. I tried using tr command in UNIX but was unable modify it to replace alternate spaces. Sample input: 0 1 2 3 4 5 Sample output: 0 1 2 3 4 5 How do we achieve this ?...

ImageMagick - Making 2 GIFs into side by side GIFs using IM convert

I have 2 GIFs that are the same length. I want to put the GIFs beside each other to have 1 GIF with both playing at the same time. I have tried to use the convert tool with: convert +append 1.gif1 2.gif output.gif However, this seems to blend all the...

Split a column's values into multiple rows awk/unix/python

Here is a sample row of input data : header: id,indicator,{(pid,days_remaining)} row: id_558314,1,{(property_66021,7),(property_24444,1),(property_285395,6)} Expected output of data : header: id,indicator,pid,days_remaining row1: id_558314,1,property_66021,7 row2: id_558314,1,property_24444,1 row3: id_558314,1,property_285395,6 Is this something easily doable using awk/unix/python? Thanks in advance. Your time and help is appreciated....

Executing DDL in compound SQL using DashDB (DB2)

I need to execute a DDL command (CREATE TABLE) with other SQL commands. See the code snippet below: CREATE TABLE test AS ( SELECT duration AS NUM1 FROM event WHERE duration IS NOT NULL )WITH NO DATA; INSERT INTO test ( SELECT duration AS NUM1 FROM event WHERE event_duration_tech IS...

Join Statement omitting entries

Using: Unix 2.6.18-194.el5 I am having an issue where this join statement is omitting values/indexes from the match. I found out the values are between 11-90 (out of about 3.5 Million entries) and I have tried to look for foreign characters but I may be overlooking something (Tried cat -v...

How to send message (in C) from one thread to another?

I'm trying to send a message from one thread to another. Each thread knows the thread ID of the other. How can I send a message between them? I've already seen some proposed solutions (message queue, anonymous pipe, etc.) but honestly I didn't get them to work. Obviously I didn't...

how to check a directory exist and can write file

I need to check directory test_file exist, if yes then need to check directory test_file is having write permission so that files will be writen in the same. this functionality i needed in shell script(ksh). What would be return code from sqlldr for single table load and multi table...

Converting values from for loop to json format

Below is a snippet of a for loop where I sort txt file names. I am then trying to save the results in a json format file. However it results in an invalid json format due to the last , inserted in obj. How could i convert to json format...

how to ssh run a tail and then send data to a mysql database

This code SSH's and then runs a tail command on a remote hots. I would now like to pass that tailed data into a mysql database using a local script called insertPerfmon.sh. How do I pass data generated in a ssh session into the local shell script insertPerfmon.sh. The local...

C++ Compile error on NetBSD '::system' has not been declared

I have some C++ code that compiles nicely on Linux but so far I'm having trouble getting it to compile correctly on NetBSD. These are my includes: #include <fstream> #include <iostream> #include <cstdlib> #include <unistd.h> #include <iomanip> #include <boost/regex.hpp> I compile with this command: g++ -v -O2 -fno-strict-aliasing -I /usr/pkg/include...

pass enter key from Java to Shell script

I am trying a Java program to run multiple commands in unix environment. I would need to pass 'ENTER' after each command. Is there some way to pass enter in the InputStream. JSch jsch=new JSch(); Session session=jsch.getSession("MYUSERNAME", "SERVER", 22); session.setPassword("MYPASSWORD"); Properties config = new Properties(); config.put("StrictHostKeyChecking", "no"); session.setConfig(config); session.connect(); Channel...

Random password generate in shell script with one special character

I have following code </dev/urandom tr -dc '[email protected]#$%&_+=' | head -c 16 which is randomly generating password perfectly. I want two changes: It should only contain 1 special symbols that i have listed there It should choose random length of the password I tried with length = $(($RANDOM%8+9)) then putting...

Doubts with for loop in Unix

Sorry if its sounds dumb but I'm currently studying for an exam to apply for a job (administrative one) and I learnt about java, php, python but not much about unix scripts. I can see it's very similar to php in structure and I have many questions about it ....

Enumerating executable files in C

I am trying to enumerate only files with the execute (+x) bit set. My code seems to list all files. It also seems to enumerate directory and the above directory which I do not want. Example: .. should_not_be_executable.sh . Is there a way to filter '..' and '.' without strstr()?...

DB2 Z/OS 10 Equivalent of AUTONOMOUS procedure

Is it possible to get the functionality of autonomous stored procedure in DB2 z/OS 10? Scenario is: attempt to execute given stored procedure must logged to our logs table even if the procedure ended with exception (rollback was performed). I was going to use autonomous stored procedure for that, but...

SFTP Processbuilder

I have to invoke unix commands from Java and I have to use ProcessBuilder. I want to login through SFTP using key authentication and I want to SFTP the file from local directory to remote location. ProcessBuilder pb = new ProcessBuilder("sftp","-oIdentityFile=privateKey","-b","commands.txt","[email protected]"); Content in commands.txt is put localFileDirectory remoteDirectory exit Is...

How to make zero the entire row except the first column, if it has a zero in any other colum in Linux?

I would like to make zero the entire row except first column, if it has a zero value in any other column. e.g., ifile.txt 1 4.5 9 2 5.0 0 3 2.4 4 4 3.1 2 5 0.0 0 6 2.4 1 7 0.0 5 I am looking my output...

Reading rsync source from file results in improper parsing of file names with white space

I wrote a simple script that searches through a specific directory defined by the variable "SCOPE" producing a list of directories that were modified within the past 24 hours printing them to a temp file. The first line of the file is deleted (to exclude the root level of the...

Calling find more than once on the same folder tree

I'm running a find command multiple times on the same group of files. The results of my find commands are usually disjoint sets, AKA I'm running find -mmin +35; find -mmin -25, and doing different things to the results. It seems sort of silly to search through the entire file...

How to remove characters before and including an underscore?

In bash scripting what's an efficient way to do the following please? var="fooo_barrrr" What is the best way to remove all characters before and including the '_' so that var becomes "barrrr" please?...

Use Unix Executable File to Run Shell Script and MPKG File

I have 2 shell scripts and 2 mpkg installer, I am trying to use an unix excitable file to run them all. here is the script I have, but it always has error message "No such file or directory" ? #!/bin/sh # Find the absolute script current path path=$( cd...

Unable to connect to mysql after automating mysql-server installation in bash script

Generates a random string: passdb="$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c${1:-2};)" Feeds passdb to debconf i.e to set random passwords for mysql users: debconf-set-selections <<< 'mysql-server mysql-server/root_password password $passdb' debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password $passdb' apt-get -y install mysql-server It get's installed successfully but I'm getting an error after...

How to extract single-/multiline regex-matching items from an unpredictably formatted file and put each one in a single line into output file?

I have a very huge file which looks like this: <a>text</a>text blah <b>data1</b>abc<b>data2</b> <b>data3</b>blahblah <c>text</c> <d>text</d> <x>blahblah<b>data4 data5 data6</b> <b>data7 </x> That is, its formatting is unpredictable. I need to extract each <b>...</b> item (it might contain multiline text!) and put every one of them in a single separate line....

find numbers divisible by 3 in csv file using shell script

I have csv file having content like below : 1|2|3 4|5|6 7|8|9 Now I would like to find the numbers which are divisible by 3 using shell scripting. I would like to use awk command for this. I am learning shell scripting. So could you please help me out to...

Why can I view some Unix executable files in Mac OS X and not others?

I am on a Macbook Pro on Mac OS X 10.10 (Yosemite). When I go to /usr/bin, git is there as a unix executable file. When I open it up in Sublime Text, all I get is unreadable machine code. However, when I open up a different Unix executable file—in...

Capture tee's argument inside piped Perl execution

How to capture piped command's argument ? I use : perl my_script.pl -some_args | tee arg_filename How to get arg_filename 's value inside my_script.pl ? CONTEXT I need to send this filename in a mail which my_script.pl sends at the end. I need to use tee because we dump huge...

search value and count number from log file

I've value in the logfile as below and I want to catch all the same number and count them and put in another file every 10 minutes. How can we do that ? [14/06/2015 14:33:55.311] - WARNING- Conflict detected between two sources !!! id1=67 id2=69 number=1193046 [14/06/2015 14:33:55.607] - WARNING-...

How to fix btrfs root inode errors

Running btrfsck, or more officially, btrfs check --repair gives this output stating that there are root inode errors. The repair command does not fix the issue and reruns will display the same output. The system is fully mountable and operational, but I cannot perform advanced operations on the partition (resizing)....

gzip and pipe to output (performance consideration)

q1) Can i check if I do a gzip -c file | encrypt (some parameters) a) does gzip print out the output line by line and pipe it to the encrypt function or b) gzip will be perform 1st, then the output will be pipe all at once to the...

C programming bi directional communication

I am trying to make something work here, i have c program where my parent process creates a pipe so he can listen request from children process. These children are created dynamically, it is never the same number. So far, i managed to send the requests to the parent through...

storing 'du' result in a variable [duplicate]

This question already has an answer here: How to return the output of program in a variable? 4 answers I am using Putty with bash-4.2. Therein, I am outputting file size with: du -m myfile.csv which returns: 1.25 myfile.csv How do i store this line in a variable so...

Zip all subdirectories using python

I am attempting to create a script that zips all of the subdirectories of a folder, then deletes the folders which have now been zipped import shutil import os loc = "foldertzipfilesin" path = "/whereparentis/" + loc + "/" dirs = os.listdir( path ) for file in dirs: name =...

Redirect output from file to stdout

I have a program that can output its results only to files, with -o option. This time I need to output it to console, i.e. stdout. Here is my first try: myprog -o /dev/stdout input_file But it says: /dev/ not writable I've found this question that's similar to mine, but...

Replace a double backslash followed by quote (\\') using sed?

I am unable to replace double backslash followed by quote \\' in sed. This is my current command echo file.txt | sed "s:\\\\':\\':g" The above command not only replaces \\' with \' it also replaces \' with ' How could I just replace exact match? Input: 'one', 'two \\'change', 'three...

How to do the expansion of variable in shell script? [duplicate]

This question already has an answer here: Bash expand variable in a variable 4 answers I hava a column in database which contains the following data: sql_proc|test_sql.sql|/home/Desktop/myfile.txt|$IMP_FILES/myFolder|convert I have fetched this into a variable and I have used the cut command with "|" as delimiter and saved each field...

How to find average and maximum in an interval using Shell [closed]

I would like to extract sum, mean and average in each 6 numbers interval from a column. I found many discussions related to this problem, but all those are for whole column. e.g. To compute sum of a column: awk '{sum+=$1} END { print sum}' To calculate Average: awk '{sum+=$1}...

Mounting GEOM_ELI Encrypted ZFS Pool as root

I have a 3 Disk RAIDz1 Pool, encrypted with AES128 in GEOM_ELI, that I have been using in FreeNAS since version 8. There have been many zpool upgrades, and over all I am very happy with ZFS. Lately however I have been growing frustrated with FreeNAS. Largely many bugs that...

Extracting columns within a range AWK

I've been trying to extract lines where a number in one columns falls within a particular range. Lets say I have a file that looks as so: chrom prediction chrom1 0.75 chrom2 0.5 chrom4 0.76 If I wanted to print lines where the prediction value was in the range from...

SQL Having statement with Date Range - The grouping is inconsistent

I'm struggling with this script. I want to write it where it'll calculate the total of instances then add the sum of those instances, however, when I run the script with the "HAVING....AND CSH.SLS_DT BETWEEN '2015-01-01' and '2015-06-16'" I get an error stating that "the grouping is inconsistent". The script...

What is wrong with these formats for Unix Shell Scripting Comparing Strings [duplicate]

This question already has an answer here: bash output command not found 4 answers I've been reading through StackExchanges posts on scripting and none of the things that I have tried are working as I would expect. Also this is my 1st shell script. # Script File Begin echo...

How to open a new terminal from my working terminal with same directory in Linux?

Is there any command to open a new terminal from my working terminal with same directory while working with ssh in Linux? e.g., I am working in the following terminal. My pwd is /home/work/Kayan01/test_run I want to open a new terminal directly from my working terminal, so that the pwd...

How to replace newlines/linebreaks with a single space, but only if they are inside of start/end regexes?

I have an extremely big (many GBs in size) file that looks like [x data1 data2 data3 data4 y] [a data5 data 6 data7 data 8 b> [x data y] ...and so on How to replace newlines (which might be surrounded by spaces) with a single space, but only if...

Modern-day Unix tools are written in what programming language? [closed]

Here is an example of using two Unix tools: ls | grep "MMC" ls and grep are two Unix tools. I believe that both ls and grep are written in C. (Correct me if I'm wrong) What programming language is used to implement modern-day Unix tools? Are modern-day Unix tools...

Multiple line search in a file using java or unix command

I want to find the following pattern in a file like subclass "Pool1" 11:22:33:44:55:66 { dynamic; } the above pattern I have to find in a file. How can I find either using java or Unix command?...

Improving a sort program's efficiency; displaying column sort % completion in terminal

I have a big pipe-delimited input file approx 6 million lines as below: 24|BBG000SJFVB0|EQ0000000009296012|OI SA-ADR|OIBR/C|US|ADR|Equity 16|BBG002PHVB83|EQ0000000022353186|BLOOM SELECT INCOME FUND|BLB-U|CT|Closed-End Fund|Equity -50|BBG000V0TN75|EQ0000000010271114|MECHEL-PREF SPON ADR|MTL/P|US|ADR|Equity 20|BBG002S0ZR60|EQ0000000022739316|DIVIDEND 15 SPLIT CORP II-RT|DF-R|CT|Closed-End Fund|Equity -20|BBG001R3LGM8|EQ0000000017879513|ING FLOATING RATE SENIOR LOA|ISL/U|CT|Closed-End Fund|Equity 0|BBG006M6SXL2|EQ0000000006846232|AA...

Identifying when a file is changed- Bash

So in my bash shell script, I have it running through a for loop. Inside the for loop, I use find "$myarray[i]" >> $tmp to look for a certain directory each time through the loop. Sometimes, it finds the variable in myarray[i] and sometimes it doesn't. When it does find...

Why is Unix/Terminal faster than R?

I'm new to Unix, however, I have recently realized that very simple Unix commands can do very simple things to large data set very very quickly. My question is why are these Unix commands so fast relative to R? Let's begin by assuming that the data is big, but not...