PowerShell tee-Object generates empty lines in output, when used in db2 commands

Question

When I use powershell tee-object cmdlet to save the output to a file, blank lines are created between each actual line. Output gets doubled and ugly, in both screen output, as well in the redirected file.

regular command, and output:

# db2 connect to sample

   Database Connection Information

 Database server        = DB2/NT64 11.5.0.0
 SQL authorization ID   = SAMUEL
 Local database alias   = SAMPLE

but, when you use Tee-Object against it... here is what happens:

# db2 connect to sample |  Tee-Object test.out


   Database Connection Information



 Database server        = DB2/NT64 11.5.0.0

 SQL authorization ID   = SAMUEL

 Local database alias   = SAMPLE

In both screen output, and also in the generated file a well:

# type test.out


   Database Connection Information



 Database server        = DB2/NT64 11.5.0.0

 SQL authorization ID   = SAMUEL

 Local database alias   = SAMPLE

--- edit --- @js2010, here is the entire hex-format for better reading.. cant paste it properly in the comments.

# format-hex test.out


           Path: E:\PowerShell_Tests\db2mon\test.out

           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   FF FE 0D 00 0A 00 0D 00 0A 00 20 00 20 00 20 00  .þ........ . . .
00000010   44 00 61 00 74 00 61 00 62 00 61 00 73 00 65 00  D.a.t.a.b.a.s.e.
00000020   20 00 43 00 6F 00 6E 00 6E 00 65 00 63 00 74 00   .C.o.n.n.e.c.t.
00000030   69 00 6F 00 6E 00 20 00 49 00 6E 00 66 00 6F 00  i.o.n. .I.n.f.o.
00000040   72 00 6D 00 61 00 74 00 69 00 6F 00 6E 00 0D 00  r.m.a.t.i.o.n...
00000050   0A 00 0D 00 0A 00 0D 00 0A 00 0D 00 0A 00 20 00  .............. .
00000060   44 00 61 00 74 00 61 00 62 00 61 00 73 00 65 00  D.a.t.a.b.a.s.e.
00000070   20 00 73 00 65 00 72 00 76 00 65 00 72 00 20 00   .s.e.r.v.e.r. .
00000080   20 00 20 00 20 00 20 00 20 00 20 00 20 00 3D 00   . . . . . . .=.
00000090   20 00 44 00 42 00 32 00 2F 00 4E 00 54 00 36 00   .D.B.2./.N.T.6.
000000A0   34 00 20 00 31 00 31 00 2E 00 35 00 2E 00 30 00  4. .1.1...5...0.
000000B0   2E 00 30 00 0D 00 0A 00 0D 00 0A 00 20 00 53 00  ..0......... .S.
000000C0   51 00 4C 00 20 00 61 00 75 00 74 00 68 00 6F 00  Q.L. .a.u.t.h.o.
000000D0   72 00 69 00 7A 00 61 00 74 00 69 00 6F 00 6E 00  r.i.z.a.t.i.o.n.
000000E0   20 00 49 00 44 00 20 00 20 00 20 00 3D 00 20 00   .I.D. . . .=. .
000000F0   53 00 41 00 4D 00 55 00 45 00 4C 00 0D 00 0A 00  S.A.M.U.E.L.....
00000100   0D 00 0A 00 20 00 4C 00 6F 00 63 00 61 00 6C 00  .... .L.o.c.a.l.
00000110   20 00 64 00 61 00 74 00 61 00 62 00 61 00 73 00   .d.a.t.a.b.a.s.
00000120   65 00 20 00 61 00 6C 00 69 00 61 00 73 00 20 00  e. .a.l.i.a.s. .
00000130   20 00 20 00 3D 00 20 00 53 00 41 00 4D 00 50 00   . .=. .S.A.M.P.
00000140   4C 00 45 00 0D 00 0A 00 0D 00 0A 00 0D 00 0A 00  L.E.............
00000150   0D 00 0A 00                                      ....

Also, your 2nd test reveals that the problem is not about using tee-object cmdlet, but actually, just piping the output causes it...

Another information, If I perform a redirect to a file, from a regular windows cmd window, the issue does not happens,

from cmd window:

E:\PowerShell_Tests\db2mon>db2 connect to sample > cmd.out

E:\PowerShell_Tests\db2mon>type cmd.out

   Database Connection Information

 Database server        = DB2/NT64 11.5.0.0
 SQL authorization ID   = SAMUEL
 Local database alias   = SAMPLE

but, performing the same redirect from a powershell session, created the double lines again:

# db2 connect to sample > pwsh.out
PS [Samuel]E:\PowerShell_Tests\db2mon
# Get-Content pwsh.out


   Database Connection Information



 Database server        = DB2/NT64 11.5.0.0

 SQL authorization ID   = SAMUEL

 Local database alias   = SAMPLE

--- end edit ---

--- edit 2 --- @js2010

# db2 connect to sample | format-hex


           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   20 20 20 44 61 74 61 62 61 73 65 20 43 6F 6E 6E     Database Conn
00000010   65 63 74 69 6F 6E 20 49 6E 66 6F 72 6D 61 74 69  ection Informati
00000020   6F 6E                                            on


           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   20 44 61 74 61 62 61 73 65 20 73 65 72 76 65 72   Database server
00000010   20 20 20 20 20 20 20 20 3D 20 44 42 32 2F 4E 54          = DB2/NT
00000020   36 34 20 31 31 2E 35 2E 30 2E 30                 64 11.5.0.0


           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   20 53 51 4C 20 61 75 74 68 6F 72 69 7A 61 74 69   SQL authorizati
00000010   6F 6E 20 49 44 20 20 20 3D 20 53 41 4D 55 45 4C  on ID   = SAMUEL



           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   20 4C 6F 63 61 6C 20 64 61 74 61 62 61 73 65 20   Local database
00000010   61 6C 69 61 73 20 20 20 3D 20 53 41 4D 50 4C 45  alias   = SAMPLE

--- end edit 2 ---

Does any one has any clue on what is going on, and how can I "fix" it ?

Thanks

Since I don't have db2, what does `format-hex test.out` looks like? OD is carriage return and OA is linefeed. — js2010, Apr 30 '20 at 13:31
it really adds the pair twice: `00000050 0A 00 0D 00 0A 00 0D 00 0A 00 0D 00 0A 00 20 00 .............. .` `00000060 44 00 61 00 74 00 61 00 62 00 61 00 73 00 65 00 D.a.t.a.b.a.s.e.` `00000070 20 00 73 00 65 00 72 00 76 00 65 00 72 00 20 00 .s.e.r.v.e.r. .` `00000080 20 00 20 00 20 00 20 00 20 00 20 00 20 00 3D 00 . . . . . . .=.` `00000090 20 00 44 00 42 00 32 00 2F 00 4E 00 54 00 36 00 .D.B.2./.N.T.6.` `000000A0 34 00 20 00 31 00 31 00 2E 00 35 00 2E 00 30 00 4. .1.1...5...0.` `000000B0 2E 00 30 00 0D 00 0A 00 0D 00 0A 00 20 00 53 00 ..0......... .S.` — Samuel Pizarro, May 01 '20 at 00:41
nope. it doesn't, and your new test now reveals that the problem is not with Tee-Object, but the problem happens for any pipe on db2 commands output. The interesting part is that If I redirect db2 cmd otputs from regular cmd window (not powershell) the issue does not happens — Samuel Pizarro, May 01 '20 at 18:31
I asked someone else about it. Also check it out. I think sfc does something similar: https://stackoverflow.com/questions/57749808/sfc-output-redirection-formatting-issue-powershell-batch — js2010, May 01 '20 at 19:50
@js2010 It looks similar to the `sfc.exe` problem with respect to using CRCRLF (sic) rather than CRLF newlines. — mklement0, May 01 '20 at 20:23
Thus, `(db2 ...) -join "\`r\`n" -replace "\`r\`n\`r\`n", "\`r\`n" | Tee-Object` may help. — mklement0, May 01 '20 at 20:26
@mklement0 and @js2010, thanks.. this really helped.. `(db2 ...) -join ...` Now, is there something else I can set at session level like the `[console]::OutputEncoding` to avoid having to add all of this hack, in my commands.. ? — Samuel Pizarro, May 01 '20 at 20:38
Ok, I got it now,, its not an encoding issue, but actually a CRCRLF pattern.. I just would like to understand why the need to perform the `-join rn` first, instead of trying to replace directly the pattern \r\r\n to \r\n ? I tried this `(db2 ...) -replace "`r`r`n", "`r`n"`, but did not work . Also, please post a proper answer, so I can mark it as answered.. Thanks — Samuel Pizarro, May 01 '20 at 21:12
@SamuelPizarro Powershell turns it into a string array without any \r\n at the end of all each element. The join is just a way to set it up as one multiline string, and then the blank lines can be edited out. — js2010, May 01 '20 at 22:31
@js2010, Ahhhh.... now that makes sense.. I was missing that small detail. makes sense now.. Thanks for the explanation! — Samuel Pizarro, May 02 '20 at 14:07

mklement0 · Accepted Answer · 2020-05-04T13:50:00.923

As your Format-Hex output implies, db2 - bizarrely - uses CRCRLF ("`r`r`n" in PowerShell terms) rather than the usual CRLF sequences ("`r`n") as newlines (to separate its output lines) - it is a behavior it shares with sfc.exe.

When you print to the display, this anomaly doesn't surface, but it does when you capture or redirect the output, such as via Tee-Object.

The workaround is to eliminate every other line, which discards the extra lines that result from PowerShell interpreting a CR ("`r") by itself as a newline too:

$i = 0
db2 ... | Where-Object { ++$i % 2 } | Tee-Object test.out

Update: You've since provided a convenient wrapper function based on this solution in your own answer.

Samuel Pizarro · Answer 2 · 2020-05-03T19:21:38.377

for other Db2 DBAs out there trying to use powershell as me..

I have created this small hack, to handle this for all my db2 ps sessions.

Edit your powershell user profile, creating an function and alias as above:

$Home[My ]Documents\PowerShell\Microsoft.PowerShell_profile.ps1 :

# db2 settings for powershell
Set-Item -Path env:DB2CLP -value "**$$**"

# Handle db2 output, avoiding doubled lines due 'CRCRLF' pattern
Function Handle-Db2 {
    $i = 0
    db2 $args | Where-Object { ++$i % 2 }
}

New-Alias -Name "db2ps" Handle-Db2

Now, if you want to use the hacked version, instead of calling db2 .... you can use db2ps ... instead and have a proper output.

# db2ps describe table employee | Tee-Object employee.out

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
EMPNO                           SYSIBM    CHARACTER                    6     0 No
FIRSTNME                        SYSIBM    VARCHAR                     12     0 No
MIDINIT                         SYSIBM    CHARACTER                    1     0 Yes
LASTNAME                        SYSIBM    VARCHAR                     15     0 No
WORKDEPT                        SYSIBM    CHARACTER                    3     0 Yes
PHONENO                         SYSIBM    CHARACTER                    4     0 Yes
HIREDATE                        SYSIBM    DATE                         4     0 Yes
JOB                             SYSIBM    CHARACTER                    8     0 Yes
EDLEVEL                         SYSIBM    SMALLINT                     2     0 No
SEX                             SYSIBM    CHARACTER                    1     0 Yes
BIRTHDATE                       SYSIBM    DATE                         4     0 Yes
SALARY                          SYSIBM    DECIMAL                      9     2 Yes
BONUS                           SYSIBM    DECIMAL                      9     2 Yes
COMM                            SYSIBM    DECIMAL                      9     2 Yes

  14 record(s) selected.


# db2ps describe table employee | Select-String "DEC"

SALARY                          SYSIBM    DECIMAL                      9     2 Yes
BONUS                           SYSIBM    DECIMAL                      9     2 Yes
COMM                            SYSIBM    DECIMAL                      9     2 Yes

It would be nice if IBM fix this odd CRCRLF behavior on db2 commands on windows. Until this not happens, enjoy!

Regards

PowerShell tee-Object generates empty lines in output, when used in db2 commands

2 Answers2

Linked