Hello all,
I have 2 very long list of elements. f.e.:
List 1
1 2 3 4 5
List 2
a b c
How can I combine the two with other. Like this:
1 a
1 b
1 c
2 a
2 b
2 c
3 a
etc.
Thank you in advance!
I made such construction, but it works very slow:(
declare -a itemid=($(cat ITEMID.txt))
declare -a elementen=($(cat elementen.txt))
for i in ${elementen[@]}
do
for b in ${itemid[@]}
do
db2 -x "select DOCID from ICMADMIN."$i" where ITEMID='"$b"'" && echo "$i"
done
done
You can save half of the memory by not storing in arrays.
set -f # for safety: no filename generation
for i in $(cat elementen.txt)
do
for b in $(cat ITEMID.txt)
do
db2 -x "select DOCID from ICMADMIN."$i" where ITEMID='"$b"'" && echo "$i"
done
done
bash even allows to replace $(cat ...) with $(< ...) .
Hi
eval echo {$(tr ' ' , <ITEMID.txt)}{$(tr ' ' , <elementen.txt)} | fmt -1
--- Post updated at 17:50 ---
It's quite possible
eval echo {0..10}{$(tr ' ' , <file2)} | fmt -1
or even such
echo {1..5}{a..c} | fmt -1
--- Post updated at 17:58 ---
echo -n {1..5}{a..c} | xargs -d' ' -I{} echo {}
--- Post updated at 18:08 ---
I was wrong, did not pay attention to the command "db2"
--- Post updated at 18:37 ---
But continue, very interesting 
eval echo {$(tr ' ' , <ITEMID.txt)}\' \'{$(tr ' ' , <elementen.txt)} | fold -w4 |
while read i b; do
db2 -x "select DOCID from ICMADMIN."$i" where ITEMID='"$b"'" && echo "$i"
done
should change fold -w4 for grep -o '\w\+ \w\+'
How about trying, given your shell offers
mapfile commandand db2 reads from stdin,
mapfile -td" " F1 < file1
mapfile -td" " F2 < file2
IFS=$'\n'
for i in ${F1
[*]}
do db2 -x <<< ${F2
[*]/#/select DOCID from ICMADMIN.$i where ITEMID= }
done
db2 -x
select DOCID from ICMADMIN.1 where ITEMID= a
select DOCID from ICMADMIN.1 where ITEMID= b
select DOCID from ICMADMIN.1 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.2 where ITEMID= a
select DOCID from ICMADMIN.2 where ITEMID= b
select DOCID from ICMADMIN.2 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.3 where ITEMID= a
select DOCID from ICMADMIN.3 where ITEMID= b
select DOCID from ICMADMIN.3 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.4 where ITEMID= a
select DOCID from ICMADMIN.4 where ITEMID= b
select DOCID from ICMADMIN.4 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.5 where ITEMID= a
select DOCID from ICMADMIN.5 where ITEMID= b
select DOCID from ICMADMIN.5 where ITEMID= c
And I managed to throw out an extra command
eval echo -en "{$(tr ' ' , <file1)}' '{$(tr ' ' , <file2)}'\n'" |
while read a b; do
echo "$a $b"
done
--- Post updated at 20:12 ---
Just how do I know the length of the string?
getconf LINE_MAX ?
--- Post updated at 20:22 ---
it yields out even with such a set
echo -en {1..30000}' '{a..z}'\n'
--- Post updated at 20:33 ---
Or this expression echo {1..5} does not expand into a string like this echo {1,2,3,4,5}
--- Post updated at 20:43 ---
issued all 30,000 lines
for((i=1;i<30000; i++)); do echo -n "$i "; done >file1
eval echo -en "{$(tr ' ' , <file1)}' '{a..z}'\n'" | while read a b; do echo "$a.$b."; done
Ok
--- Post updated at 21:38 ---
for i in $(eval echo {$(tr ' ' , <file1)}_{$(tr ' ' , <file2)}); do
echo "index ${i%_*} value ${i#*_}"
done
I would think that you are spending a lot of time in your loop repeatedly opening the database connection. Could you build all the queries into a single command file and execute one DB read? The output could be caught to a file or into a variable and then processed further. You might need to add extra columns to the output so you can tell which is from which query if that's necessary.
Can you tell us more about the overall aim of the process and perhaps we can help you towards a more efficient solution. The variable names don't give much away. Perhaps more meaning ful names (even if it takes marginally longer to type) will be far clearer to understand later on.
Kind regards,
Robin
Pertaining to post #1: Try:
join -12 -22 -o 1.1,2.1 <(xargs -n1 <file1) <(xargs -n1 <file2)
Immediately apologize for this disgrace, but it should work
eval echo -en "db2' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(tr ' ' , <file1)}'\" where ITEMID=\"'{$(tr ' ' , <file2)}'\n'" | bash
Hello, thank you for your help.
the command
eval echo -en "db2' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(tr ' ' , <ITEMID.txt)}'\" where ITEMID=\"'{$(tr ' ' , <et.txt)}'\n'"
make an output:
If 'A1001001A18K15A90630F85271' is not a typo you can run the following command to lookup the package that contains the binary:
command-not-found A1001001A18K15A90630F85271
-bash: A1001001A18K15A90630F85271: command not found
Ok
Try it like this, which will output?
eval echo -en "echo' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(tr ' ' , <ITEMID.txt)}'\" where ITEMID=\"'{$(tr ' ' , <et.txt)}'\n'"
--- Post updated at 12:53 ---
That's what I get
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="c
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="c
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="c
...
--- Post updated at 12:54 ---
Is the file correct? <et.txt
--- Post updated at 13:02 ---
But please do not take my examples too seriously. Such lines are very difficult to debug and maintain.
You are pursue practical goals, and therefore it is better to pay attention to #2 and #6 post. IMHO
I think the main problem of a large execution time is precisely in the number of calls to the DB2 command
Here is the output:
If FHBPDM_DOC_05001 is not a typo you can run the following command to lookup the package that contains the binary:
command-not-found FHBPDM_DOC_05001
Here is as example:
tail ITEMID.txt
A1001001A19D02A60148J20560
A1001001A19D02A60149F20562
A1001001A19D02A60149F20566
A1001001A19D02A60149F20564
A1001001A19D02A60149F20568
A1001001A19D02A60150B20570
A1001001A19D02A60150B20572
A1001001A19D02A60150B20574
A1001001A19D02A60150E20578
A1001001A19D02A60150E20576
tail et.txt
NNDTY4_DOC_05001
NNDTY5_DOC_05001
NNDTY6_DOC_05001
NNSPDM_DAT_05001
NNSPDM_DOC_04001
NNSPDM_PRI_01001
NNSTDM_DOC_04001
NNSTDM_PRI_01001
NZGPIC_DAT_01001
NZGPIM_DAT_01001
The thing is that the files do not consist of a list as stated, but of the lines!
--- Post updated at 13:37 ---
it is very easy to solve
eval echo -ne "echo' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(tr '\n' , <ITEMID.txt)}'\" where ITEMID=\"'{$(tr '\n' , <et.txt)}'\n'"
--- Post updated at 13:42 ---
It works
eval echo -ne "echo' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(paste -sd, ITEMID.txt)}'\" where ITEMID=\"'{$(paste -sd, et.txt)}'\n'"
now it's almoust works:
Output:
echo -x "select DOCID from "ICMADMIN.A1001001A19B20C31636F41449" where ITEMID="FFCPYA_DAT_01001
It should be like:
db2 -x "select DOCID from ICMADMIN.FFCPYA_DAT_01001 where ITEMID='A1001001A19D02A60150B20570'"
eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(paste -sd, ITEMID.txt)}'\" where ITEMID=\"'{$(paste -sd, et.txt)}'\n'" | bash
--- Post updated at 14:07 ---
Another variant
bash <(eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(paste -sd, ITEMID.txt)}'\" where ITEMID=\"'{$(paste -sd, et.txt)}'\n'")
This command do it
eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN.{$(paste -sd, et.tst)}' where ITEMID='\'{$(paste -sd, itid.tst)}\''\"\n'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20570'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20572'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20574'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150E20578'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150E20576'"
db2 -x "select DOCID from ICMADMIN.NNSTDM_DOC_04001 where ITEMID='A1001001A19D02A60150B20570'"
but if I try do
bash <(eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN.{$(paste -sd, et.tst)}' where ITEMID='\'{$(paste -sd, itid.tst)}\''\"\n'")
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
but the db2 connection is almost exists.
If I take a command with cut/paste i got no errors.
Well, I can absolutely accurately reproduce
eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN.{$(paste -sd, et.tst)}' where ITEMID='\'{$(paste -sd, itid.tst)}\''\"\n'"
db2 -x "select DOCID from ICMADMIN.NZGPIM_DAT_01001 where ITEMID='A1001001A19D02A60150E20576'"
Now hopefully right
The echo is correct, now should I make it executable.
The other variant works too:
join -12 -22 -o 1.1,2.1 <(xargs -n1 <et.tst) <(xargs -n1 <itid.tst) |
while read a b; do
eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN."$a"' where ITEMID='\'"$b"\''\"\n'"
done
join -12 -22 -o 1.1,2.1 et.tst itid.tst |
while read a b; do
db2 -x "select DOCID from ICMADMIN.$a where ITEMID='$b'"
done
Reread I fixed the code
I made the script with
#!/bin/bash -x
+ join -12 -22 -o 1.1,2.1 et.tst itid.tst
+ read a b
+ db2 -x 'select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='\''A1001001A19D02A60150B20570'\'''
SQL1024N A database connection does not exist. SQLSTATE=08003
My advice. Just make sure that one line is executed without any cycles.
Then the working sample will be conveniently substitute in script
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20570'"
I have nothing to check, I have not installed db2