Showing posts with label computing. Show all posts
Showing posts with label computing. Show all posts

2023-07-02

Fixing Problems After Debian Bullseye -> Bookworm

First three items are from upgrade of my shack computer.

Item #4 is from upgrade of my main desktop computer.

Item #5 is from my home server. 

Item #6 occurred on both the main desktop computer and the home server.

 

Following an error-free upgrade from debian bullseye to bookworm, I encountered the following problems:

1. Problem: The DejaVu Sans Mono font was no longer available.

Solution: I installed the package fonts-dejavu.

2. Problem: Even though the termName was set to xterm-256color in the ~/.Xresources file, the actual value of TERM in an xterm was "xterm".

Solution: Explicitly export TERM=256-color in the ~/.bashrc file. 

Comment: This looks like a bug; setting termName in the resources file has always worked in the past.

3. Problem: core dumps were no longer written to the current working file. (In fact, it wasn't clear where they were being written.)

Solution: Add the line:

kernel.core_pattern = core

to /etc/sysctl.conf.

Comment: I have been using versions of UNIX since the late 1980s. Every single version until debian bookworm would automatically dump core to the current working directory. This seems to be Yet One More example of the madness of systemd taking everything over. Why debian would change the default behaviour of something on which so many people rely seems ridiculous. But then, so did their decision to support systemd.

4. Problem: autotrash has been removed by the upgrade.

The autotrash website says that the script can be installed with:

 pip install --user autotrash

However, that produces:

 [ZB:bin] pip install --user autotrash
error: externally-managed-environment

× This environment is externally managed
╰─> To install Python packages system-wide, try apt install
   python3-xyz, where xyz is the package you are trying to
   install.
    
   If you wish to install a non-Debian-packaged Python package,
   create a virtual environment using python3 -m venv path/to/venv.
   Then use path/to/venv/bin/python and path/to/venv/bin/pip. Make
   sure you have python3-full installed.
    
   If you wish to install a non-Debian packaged Python application,
   it may be easiest to use pipx install xyz, which will manage a
   virtual environment for you. Make sure you have pipx installed.
    
   See /usr/share/doc/python3.11/README.venv for more information.

note: If you believe this is a mistake, please contact your Python installation or OS distribution provider. You can override this, at the risk of breaking your Python installation or OS, by passing --break-system-packages.
hint: See PEP 668 for the detailed specification.
[ZB:bin]

Solution:

Install the python3-full package. Then execute:

[ZB:~] python3 -m venv ~/.venvs/autotrash
[ZB:~] ~/.venvs/autotrash/bin/python -m pip install autotrash
Collecting autotrash
 Downloading autotrash-0.4.5-py3-none-any.whl (22 kB)
Installing collected packages: autotrash
Successfully installed autotrash-0.4.5
[ZB:~]

Now autotrash can be run as:

 ~/.venvs/autotrash/bin/autotrash

5. Problem: no networking.

This computer uses two ethernet ports: one to my ISP and hence to the Internet; one to my home LAN. On completing the upgrade (without error) and rebooting the machine, neither network was functioning.

The networking was originally configured as part of the process of installing stretch on this machine, probably about six years ago; networking has worked flawlessly since then, until this upgrade.

Solution:

The directory /etc/NetworkManager/system-connections directory contains two files, rather oddly called:

  • 'Wired connection enp11s0(eth0).nmconnection'
  • 'Wired connection enp12s0(eth1)'

The contents of these files are:

Wired connection enp11s0(eth0).nmconnection:

[connection]
id=Wired connection enp11s0(eth0)
uuid=8e308949-5232-47aa-b9b6-8aab6369ec4d
type=ethernet
permissions=

[ethernet]
mac-address-blacklist=

[ipv4]
address1=209.97.232.18/24,209.97.232.1
dns=127.0.0.1;209.97.224.2;209.97.224.3;
dns-search=
method=manual

[ipv6]
addr-gen-mode=stable-privacy
dns-search=
method=auto

[proxy]

Wired connection enp12s0(eth1):

[connection]
id=Wired connection enp12s0(eth1)
uuid=251dd901-f8b7-42ee-b568-b15ba565a81b
type=ethernet
permissions=

[ethernet]
mac-address=D8:50:E6:C2:76:03
mac-address-blacklist=

[ipv4]
address1=192.168.0.1/24
dns-search=
method=manual

[ipv6]
addr-gen-mode=stable-privacy
dns-search=
method=auto

The fix is to add a MAC address to the first file.

I note that it is odd that this is necessary: as enp12s0 is mapped directly to a particular port by the MAC address, Network Manager should know that enp11s0 should therefore be mapped to the other port. It has worked that way on this machine since stretch, but apparently this behaviour has changed in bookworm.

It took quite a while to figure this out; more details of the flailing around can be found in the threads that start at:

https://lists.debian.org/debian-user/2023/09/msg00024.html and 

https://lists.debian.org/debian-user/2023/09/msg00061.html.

There is also an official response to the issue at:

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1051086,

which I note is remarkably unhelpful (if only because it suggests use of a utility that is not part of debian stable; but it also suggests that the problem is due to kernel timing, which is hard to believe given the discussion in the threads on lists.debian.org -- can a problem that depends on timing of the order of many seconds really be due to a change in kernel timing? -- also, the suggested fix seems less clean than the one I provide above; and, in any case, if a human can figure out which connection goes with which port, Network Manager should have been able to do the same. And Network Manager should in no circumstance have tried to assign two connections to each other, as is described in the first lists.debian.org post; the worst that should have happened is that the connection without a MAC address should have failed, and a useful error message provided).

6. Problem: Errors occur when attempting to connect to remote computers over ssh.

When trying to connect to remote systems over ssh, various error messages would be received. This is a typical such message:

Unable to negotiate with 77.109.148.18 port 22: no matching host key type found. Their offer: ssh-dss,ssh-ed25519

The messages all suggested that ssh was unable to find a common acceptable key type.

It seems that the default types of keys that are deemed acceptable must have changed in bookworm. The easiest fix is simply to restore the old keys types as being acceptable, by adding the following lines to ~/.ssh/config:

PubkeyAcceptedKeyTypes +ssh-rsa
PubkeyAcceptedKeyTypes +ssh-dss

HostKeyAlgorithms +ssh-rsa
HostkeyAlgorithms +ssh-dss

 

2023-03-17

arrl.net and the ARRL -- or: How Not to Treat Your Members

(I apologise for the inconsistent size of the font in the main text of this post. I wasted twenty minutes of my life trying to force blogger.com to eliminate most of its auto-generated convoluted HTML so that I could then manually rationalise the look of the text, but I was forced to admit defeat. I refrain, with some difficulty, from ranting about the blogger.com interface for creating blog entries.)

The arrl.net Service

The February, 1999 issue of the ARRL's journal QST, included the following item (the above link may require membership in ARRL for access) in the Happenings column:

[I note that the ARRL attempts a limitation --

-- that is grossly over-reaching and, in particular, makes no allowance for fair use -- a legal concept that, while a little blurry around the edges, would certainly apply to, as here, the duplication for noncommercial purposes of a small part of one column on one page of a magazine of well over 100 pages.]

Like many active hams, I saw the value of being able to use an e-mail address of the form <callsign>@arrl.net for day-to-day e-mail, and I signed up for the service.

For more than twenty years, the address N7DR@arrl.net has been my usual e-mail address, used for communication with friends, family and many other categories of correspondent.

[In passing, I should note that a couple of people have recently pointed out that the above announcement made no mention of sending e-mail from the arrl.net domain. However, the distinction is rather disingenuous. In 1999, there was no expectation, or even general notion, that a valid e-mail address could not be used as a matter of course for both sending and receiving e-mail. Indeed, even now, it is hard to see the practical purpose of an address that can be used solely for receiving personal e-mail on a day-to-day basis. Had there been any expectation in 1999 that an address would be so limited, it's hard to envisage many members signing up. Or, indeed, that the ARRL would not have included a specific warning not to use the address for sending e-mails. And, in practice, the arrl.net addresses worked perfectly fine from the time of the service's introduction for both sending and receiving communication. And it's also difficult to understand how it could have been otherwise: the technical method that was eventually introduced to implement the limiting of e-mails in this manner was not even introduced by the IETF until the publication of RFC 4408 in April, 2006 -- and even then the RFC was explicitly stated as being experimental (i.e., explicitly not an IETF standard).]

So, for more than twenty years, arrl.net addresses worked as one would expect: e-mails sent to your arrl.net address would be forwarded to a server from which they could be downloaded using whatever protocol(s) that server supported (typically POP3 and/or IMAP); and, if you configured your e-mail program (technically, a Mail User Agent, or MUA) so that the From: header contained your arrl.net address, you had every expectation that the associated e-mail would be delivered correctly by whatever SMTP server one used to relay outbound e-mails.

And then came 2023, and an unannounced policy change by the ARRL (at least, I have not been able to find any warning, explanation, discussion or announcement of the change -- or even an after-the-fact description of it, to say nothing of any kind of motivation for it).

The Symptom(s)

So what were the symptoms of the problem that arose because of this change? 

The first indication of a problem I saw was that an e-mail for a gmail.com user was bounced by that domain's e-mail server. Now, for gmail to bounce a legitimate e-mail is hardly unknown [as far as I can tell; despite the fact that they almost certainly scan the contents of every e-mail they handle, they don't actually use that information for determining whether a given e-mail is "spam" -- technically, what we generally refer to as "spam" is "Unsolicited Commercial E-mail", or UCE)]. Historically, gmail would occasionally decide that all e-mails from the server through which I route outbound e-mail is UCE, even though a glance at any of my personal e-mails would have been sufficient to determine that, while they are indeed e-mails (!), they are generally not "unsolicited commercial" e-mails. Anyway, every now and then, gmail would bounce my e-mails for a day or two before suddenly allowing them again. This would happen perhaps every year or so, usually for a couple of days, before gmail would revert to accepting e-mails from the server. So it was annoying, and demonstrative of sloppy coding, but not really a major issue.

But now the symptom was different. The bounce message from gmail.com said:

550-5.7.26 The MAIL FROM domain [arrl.net] has an SPF record with a hard fail 550-5.7.26 policy (-all) but it fails to pass SPF checks with the ip: 550-5.7.26 [64.62.234.38]. To best protect our users from spam and phishing, 550-5.7.26 the message has been blocked. Please visit 550-5.7.26 https://support.google.com/mail/answer/81126#authentication for more 550 5.7.26 information. g32-20020a9d12a3000000b0068cd21180c7si2618456otg.140 - gsmtp

As usual with bounce messages from gmail, the included link was useless. But the text of the message is sufficient to describe the problem in detail: the SPF record for arrl.net suddenly was indicating that a legitimate e-mail from the arrl.net domain could come only from particular pre-defined servers. First, what exactly is an SPF record?

RFC 7208, published in April, 2014, is the IETF standards-track document defining the Sender Policy Framework (SPF). The SPF is in tended to allow the owner of a domain to place restrictions on the servers from which e-mail claiming to be from that domain are allowed to be received. In other words, a company can use SPF to inform the recipient server whether an e-mail purporting to come from its domain as actually being received from a server that is permitted to send e-mails from that domain. Great for a company... but hardly the milieu in which the ARRL's e-mail system operates, as there are hundreds (thousands?) of members legitimately sending their @arrl.net e-mails from a multitude of equally-legitimate SMTP servers, none of which are known to, or under the control of, the ARRL

Sender Policy Framework (SPF)

SPF is defined in RFC 7208; a good less-technical explanation of the parts that matter to us may be found here.

The basic idea is that the recipient e-mail server may (there is no requirement that it do this, and many e-mail servers are configured not to do so) issue a Domain Name System (DNS) query against the domain of the e-mail's From: address. The response from DNS may include an SPF record (again, there is no requirement that such a record be included in a domain's DNS configuration).

So, suppose that an e-mail server receives an e-mail from N7DR@arrl.net. It may query DNS and receive, as part of the response the following entry:

arrl.net        text = "v=spf1 include:spf.protection.outlook.com include:pobox.com -all"

This tells the e-mail server that the owner of the arrl.net domain (i.e., the ARRL) has included a record complying with SPF version 1 (as of this writing, that is the only extant version).

Skipping some details, the two "include" fields tell the server to query those domains as well; typically, that query informs the e-mail server that arrl.net emails received from machines in those domains are to be accepted. Unless one happens to be using outlook.com or pobox.com as one's outbound e-mail server, these "include" fields may be ignored for our purposes.

The important part is that last field:  -all.

The above-referenced document describes that field well. It says, in part,:

"-all " is the part of the record that indicates what is recommended to do if the sending IP address does not match any of the ones in the record. This is determined by whomever publishes the SPF portion to the DNS record, such as the owner of the domain.

So, in our case, this is what the ARRL has decided should be done with @arrl.net e-mails that do not come from outlook.com or pobox.com.

The document continues by describing what that field means:

  1. Types of rejection levels:
  • -all (reject or fail them - don't deliver the email if anything does not match)
  • ~all (soft-fail them - accept them, but mark it as 'suspicious')
  • +all (pass regardless of match - accept anything from the domain)
  • ?all (neutral - accept it, nothing can be said about the validity if there isn't an IP match)
Most records will have a "~all" listed in the SPF record because the domain owner leaves room for the possibility of a new server getting created and might forget to update the SPF record with the new IP address of that server. This also allows for regular machines to send email without causing too much of an interruption.

Very large domains such as gmail.com have "?all" in their records to leave it up to the recipient to determine what to do with the email when received.

So, since the ARRL has no idea about which machines are originating the e-mails from all their members who use @arrl.net addresses, the one thing they should NOT be doing is to set that value to "-all". As the ARRL really can't say anything about the legitimacy of any particular @arrl.net e-mail "?all" would seem to be the correct value; but "+all" would seem to be a reasonable alternative; less reasonable would be "~all", since a recipient system might well regard anything marked as "suspicious" as to be treated differently from normal e-mail, and there is really no basis for suspicion -- certainly no basis for the ARRL to suggest a priori that it is a suspicious e-mail.

But, as we can see, the ARRL is in fact claiming that @arrl.net e-mails from other than outlook.com and pobox.com are to be summarily rejected.

No wonder, then, that suddenly e-mail servers are (surprise, surprise) rejecting legitimate personal @arrl.net e-mails.

Following the first appearance of a problem sending e-mails to @gmail.com accounts, I sent test e-mails to a number of other domains, and found that results were all over the show.

Some e-mails were delivered as normal. Presumably the destination servers at these addresses did not perform SPF checking.

Some e-mails were delivered, but with subject lines amended so as to indicate that the e-mail was spam, even though the e-mail could not possibly be regarded as being of an unsolicited commercial nature. [Why it is legal for a server to amend the contents of an e-mail subject line is a mystery to me, but apparently it is.] These servers seemed to perform an SPF check, but then ignore the ARRL's explicit instructions to drop the e-mail and instead treated the "-all" in the SPF record as if it were "~all". One can see the argument for this behaviour, even though it is non-conformant.

And some e-mails were simply silently dropped. Which has the sole merit of being in accordance with the ARRL's misguided instructions.

So the end result of the experiment was that, basically, an @arrl.net e-mail may or may not be delivered, may or may not be bounced, and may or may not be treated as spam. In other words, the address becomes, at best, unreliable -- and, in practice, more or less incompatible with ordinary day-to-day use.

So, what does one do in this situation? It turns out that what one does NOT do is to contact the ARRL to try to get them to fix this mess (of their own making).

Interaction with the ARRL

So, the first stumbling block was pretty basic: how does one contact someone at the ARRL to report an issue with their e-mail service? You'd think that it would be pretty easy to find the contact information, but it's not. There is a handy "Contact us" link on the ARRL home page. That link takes one to a contact page with a long list of departments (I refrain from asking why on Earth the League needs so many departments, and how it affords them). Looking down the list of department names, the only one that seems appropriate is "Membership & Benefits"...but that leads one merely to a membership application and e-mail links to report a change of address or an issue with delivery of QST. So one is forced to wander around the web site in search of the desired contact information, a procedure that uncomfortably resembles the Colossal Cave Adventure.

Eventually, I stumbled across the Member Support menu item on the Membership page (whoever designed the site seems to have been oblivious to the fact that someone who is already a member is hardly likely to consider that the Membership page, whose Tool Tip says simply "Join ARRL", is the place to look for assistance with e-mail.), and that menu item takes one, non-obviously, to a page labelled "Member FAQ". Buried in the text for the second of these FAQs (if they're really frequently asked, one wonders why the League made the answers so difficult to find), one find the text: For further assistance with your @arrl.net forwarding service, followed by a "contact us" link to: circulation@arrl.org. What the circulation desk has to do with the support for arrl.net addresses is a mystery, but by this time I was sufficiently pleased simply to have found what seemed to be an official link that I pondered the mystery for only a brief time before sending an e-mail detailing the problem to the listed address.

Well, the niggling concern that there was no reason a priori to associate competence at dealing with circulation matters with that required for handling e-mail issues was quickly proven to be well founded. I'm sure that the person at circulation@arrl.net meant well, but unfortunately the best of intentions are no substitute for competence. A response was quickly forthcoming. Unfortunately, that response consisted of the following:

Thank you for reaching out to us.  I just sent  a test message and it did not 
bounce back.  Please confirm receipt of that message in your xxx@xxxx.xxx account.

In other words, to respond to an issue regarding the sending of e-mails, they tested the reception of an e-mail. I explained the issue again, and this time a week went by without response. By this time I had worked my through the details of the problem as described above, so I sent a new e-mail, suggesting that the problem be put before someone who understood SPF records. In response, I was told merely:

I am following up with IT

with no further details, nor any explanation as to why I had heard nothing in the meantime.

Shortly afterwards, I received an e-mail from ARRL support at the rather peculiar and hardly-confidence-inducing non-ARRL e-mail address: support@22454831.hubspot-inbox.com, to confirm that a support request had been filed and it was being "reviewed". Like the e-mail from circulation@arrl.org, it gave no details as to the contents of the request for support.

So at this point, I was left dangling, with no real confidence that a competent support request has been filed. Nor with whom it had been filed. Nor how I could have filed a support request myself, thereby skipping the step that involved a person at ARRL who, it seemed, did not really understand the issue.

Three days later I received an e-mail from the support address:

Your ticket has been closed. Thanks for contacting our support team.
We hope that your issue has been resolved to your satisfaction. 

So: no information about the wording of the original support request contained (so I wasn't particularly confident that the problem had been reported correctly); no information about what the problem was determined to be; no information about how the problem was resolved; no information about any testing that had been performed to confirm that the issue had been corrected. In fact, not a thing about that brief communication seemed remotely commensurate with the professional competence that one would reasonably expect after reporting a technical issue.

Obviously, the thing to do was to test their "resolution" of the issue. So I sent an e-mail to a gmail account and... surprise, surprise, no change at all. The e-mail bounced, with exactly the same error message as before. Not to my surprise, looking at the SPF entry for arrl.net confirmed that no change had been made.

The e-mail from the support people contained a weird non-sentence that said simply "Request details" on a line by itself. I'm not sure how one is supposed to interpret those two words, but I took them to mean that I could request details of what had been done to resolve the ticket. So I sent an e-mail that included, in the relevant part, this text:

Yes, I request details. You say that the ticket is closed, but provide no 
explanation of what you have done, or why you think that something has changed.

I just sent a test e-mail here, and the behaviour is precisely as I reported 
before, and the problem remains exactly as it was.

As far as I can tell here, absolutely no change has taken place in the course 
of the past week.

I don't intend to be rude, but I have received nothing from the ARRL that 
gives me any confidence that anyone there either understands the problem or 
how to fix it, despite my providing what I believe was a clear description of 
the problem, along with its cause and what needed to be done to eliminate the 
problem.

In response to that, I received exactly the same e-mail that they'd sent before saying when the original request for support had been filed from circulation@arrl.org: 

Your request has been received and is being reviewed by our support team.

Four days later (you can guess where this is going), I received an e-mail precisely the same as the one I'd received before, saying:

Your ticket has been closed. Thanks for contacting our support team.
We hope that your issue has been resolved to your satisfaction.

And, of course, testing the situation revealed no change whatsoever. So I sent an e-mail to the support address, with a copy to circulation@arrl.org:

Why do I get the impression that there is no human there at all? 1. I requested details as to what action you had taken. You did not provide any. 2. You say: We hope that your issue has been resolved to your satisfaction. But I already informed you that there was no change in status at all. I just performed another test, and the problem remains. It is evident that -- if you changed anything at all -- you did not go to the trouble of testing whatever you changed 🙁 Do I sound frustrated? I am. How do I escalate this to a real live human being who can actually understand and fix the problem???
I never heard anything further. And have heard nothing further to this day. 

The Work-Around

Given the utter lack of meaningful response from the ARRL, the last, desperate remedy is to purchase an account at one of the companies that are permitted by the Sender Policy record to forward e-mail from an arrl.net address. I purchased a basic account a pobox.com ($20 per year), reconfigured my SMTP server to forward the e-mails from my arrl.net address to pobox.com, and, hey presto! suddenly I could send e-mails to all the addresses that had become problematic when I was forwarding through my normal service (I continue to use the original service for all my other (i.e., non-arrl.net) e-mail addresses).

Of course, this will last only until such time as the ARRL unilaterally changes its policy again and removes the "include:pobox.com" field in the SPF record; which, as precedent has shown, is liable to happen without warning or explanation at any time.

How most members using the service were ever supposed to figure any of this out is a mystery. As are so many things about this whole mess; springing immediately to mind, for example, are the questions:

1. Why did the ARRL decide to change the SPF record so as to eliminate all e-mail servers except those run by two particular commercial entities?

2. Who at the ARRL actually made that decision?

3. Why were those particular entities chosen?

4. Why was the change made without warning?

5. Why was the change made without any notification or explanation in QST?

6. Why was no detailed step-by-step how-to guide issued to help members understand what the ARRL has done and how to work around the problems their action has caused?

7. Why is the designated contact person for e-mail issues at the ARRL the person responsible for unrelated issues related to QST circulation?

8. Who actually is responsible for support of this service at ARRL?

9. Why is there a way in which ARRL staff can open support tickets, but no method is provided that allows members to do so?

10. Why are members not permitted to see the contents of support tickets filed by staff in response to issues raised by members?

11. Does the technical support actually do anything to fix problems, and, if so, do they test the solution? (Obviously, the answer was "No" to at least the latter question in my case.)

12. Why do e-mails from the technical support staff come from an address that has nothing to do with the ARRL (so that many people might easily automatically filter such e-mails unknowingly to a bit bucket without ever seeing them)?

13. When the technical support staff close a ticket, why do they not describe what they have done and how they have tested the purported solution?

14. Why do the technical support staff simply ignore requests from members for details as to the remediation they have performed when they claim to have fixed an issue?

I note that throughout the whole saga, not even a hint of a vague explanation was provided to me as to why the ARRL precipitated this problem. Maybe there was a legitimate reason. In fact, I hope there was.

(I note that the only reason that springs immediately to my mind is gratuitously pernicious: that they have put themselves in a position to offer a separate and distinct service to members to process outbound arrl.net e-mails -- thereby working around a block that they have themselves emplaced -- and for which they could charge an additional fee.)

I'm afraid that. as a long-time member of the ARRL, I find the combination of incompetence and arrogance displayed by its reaction to a member's legitimate issues with a service that has functioned correctly for more than twenty years to be inexcusable, even if it transpires that there was in fact a good reason for the change in their Sender Policy that precipitated all the problems. From my conversations with other amateurs preceding these events, it is clear that the ARRL has enough problems with its image and perceived competence among rank-and-file members (and non-members), without the need to create more.

2021-12-02

Debian Bullseye, Samba and WIndows XP

Following an upgrade here from Debian Buster to Bullseye, I discovered that a Windows XP computer on my home network could no longer access a samba share on the upgraded system, even though (or possibly because, although I doubt it) I did not allow the upgrade to change the /etc/samba/smb.conf file. After a bit of messing around, I discovered that the solution is easy, although apparently undocumented as yet, at least officially. I added the line:

server min protocol = NT1

to the [global] portion of the file, and then restarted samba:

systemctl restart smbd.service

From that point on, I found that the XP machine was once more able to reach the samba shares correctly.

I note that the inability to browse Bullseye samba shares from XP machines that previously could browse Buster shares on the same server machine has been reported as Debian bug 948671, with the above workaround now documented in an associated submission by me.

2020-12-30

Creating Local FCC Database From Version 5 Data

 

This is a followup to this post, required because the FCC has changed the format of their files from version 4 to version 5.

The contents of the eight FCC files are now (as described in the version 5 document):

Amateur
[AM] -- unchanged from version 4
1   Record Type [AM]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Operator Class              char(1)
7   Group Code                  char(1)
8   Region Code                 tinyint
9   Trustee Call Sign           char(10)
10  Trustee Indicator           char(1)
11  Physician Certification     char(1)
12  VE Signature                char(1)
13  Systematic Call Sign Change char(1)
14  Vanity Call Sign Change     char(1)
15  Vanity Relationship         char(12)
16  Previous Call Sign          char(10)
17  Previous Operator Class     char(1)
18  Trustee Name                varchar(50)

Comments
[CO] -- unchanged from version 4
1   Record Type [CO]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Comment Date                mm/dd/yyyy
6   Description                 varchar(255)
7   Status Code                 char(1)
8   Status Date                 mm/dd/yyyy

Entity
[EN]
1   Record Type [EN]                char(2)
2   Unique System Identifier        numeric(9,0)
3   ULS File Number                 char(14)
4   EBF Number                      varchar(30)
5   Call Sign                       char(10)
6   Entity Type                     char(2)
7   Licensee ID                     char(9)
8   Entity Name                     varchar(200)
9   First Name                      varchar(20)
10  MI                              char(1)
11  Last Name                       varchar(20)
12  Suffix                          char(3)
13  Phone                           char(10)
14  Fax                             char(10)
15  Email                           varchar(50)
16  Street Address                  varchar(60)
17  City                            varchar(20)
18  State                           char(2)
19  Zip Code                        char(9)
20  PO Box                          varchar(20)
21  Attention Line                  varchar(35)
22  SGIN                            char(3)
23  FCC Registration Number (FRN)   char(10)
24  Applicant Type Code             char(1)
25  Applicant Type Code Other       char(40)
26  Status Code                     char(1)
27  Status Date                     mm/dd/yyyy
28  3.7 GHz License Type            char(1)
29  Linked Unique System Identifier numeric(9,0)
30  Linked Call Sign                 char(10)
 
Application/License Header -- unchanged from version 4
[HD]
1   Record Type [HD]                            char(2)
2   Unique System Identifier                    numeric(9,0)
3   ULS File Number                             char(14)
4   EBF Number                                  varchar(30)
5   Call Sign                                   char(10)
6   License Status                              char(1)
7   Radio Service Code                          char(2)
8   Grant Date                                  mm/dd/yyyy
9   Expired Date                                mm/dd/yyyy
10  Cancellation Date                           mm/dd/yyyy
11  Eligibility Rule Num                        char(10)
12  Reserved                                    char(1)
13  Alien                                       char(1)
14  Alien Government                            char(1)
15  Alien Corporation                           char(1)
16  Alien Officer                               char(1)
17  Alien Control                               char(1)
18  Revoked                                     char(1)
19  Convicted                                   char(1)
20  Adjudged                                    char(1)
21  Reserved                                    char(1)
22  Common Carrier                              char(1)
23  Non Common Carrier                          char(1)
24  Private Comm                                char(1)
25  Fixed                                       char(1)
26  Mobile                                      char(1)
27  Radiolocation                               char(1)
28  Satellite                                   char(1)
29  Developmental or STA or Demonstration       char(1)
30  InterconnectedService                       char(1)
31  Certifier First Name                        varchar(20)
32  Certifier MI                                char(1)
33  Certifier Last Name                         varchar(20)
34  Certifier Suffix                            char(3)
35  Certifier Title                             char(40)
36  Female                                      char(1)
37  Black or African-American                   char(1)
38  Native American                             char(1)
39  Hawaiian                                    char(1)
40  Asian                                       char(1)
41  White                                       char(1)
42  Hispanic                                    char(1)
43  Effective Date                              mm/dd/yyyy
44  Last Action Date                            mm/dd/yyyy
45  Auction ID                                  integer
46  Broadcast Services - Regulatory Status      char(1)
47  Band Manager - Regulatory Status            char(1)
48  Broadcast Services - Type of Radio Service  char(1)
49  Alien Ruling                                char(1)
50  Licensee Name Change                        char(1)
51  Whitespace Indicator                        char(1)
52  Operation/Performance Requirement Choice    char(1)
53  Operation/Performance Requirement Answer    char(1)
54  Discontinuation of Service                  char(1)
55  Regulatory Compliance                       char(1)

History
[HS] -- unchanged from version 4
1   Record Type [HS]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Log Date                    mm/dd/yyyy
6   Code                        char(6)

License Attachment
[LA] -- unchanged from version 4
1   Record Type [LA]            char(2)
2   Unique System Identifier    numeric(9,0)
3   Call Sign                   char(10)
4   Attachment Code             char(1)
5   Attachment Description      varchar(60)
6   Attachment Date             mm/dd/yyyy
7   Attachment File Name        varchar(60)
8   Action Performed            char(1)

Special Condition
[SC] -- unchanged from version 4
1   Record Type [SC]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Special Condition Type      char(1)
7   Special Condition Code      int
8   Status Code                 char(1)
9   Status Date                 mm/dd/yyyy

License Free Form Special Condition
Position Data Element Definition
[SF] -- unchanged from version 4
1   Record Type [SF]                    char(2)
2   Unique System Identifier            numeric(9,0)
3   ULS File Number                     char(14)
4   EBF Number                          varchar(30)
5   Call Sign                           char(10)
6   License Free Form Type              char(1)
7   Unique License Free Form Identifier numeric(9,0)
8   Sequence Number                     integer
9   License Free Form Condition         varchar(255)
10  Status Code                         char(1)
11  Status Date                         mm/dd/yyyy
The following extract from the code that creates the output database maps these on a one-to-one basis to internal identifiers (the four new fields in the [HD] records don't seem to be important -- at least for now -- so there is no change in the output as compared to the processing of version 3 files, although the new fields are processed):

[AM]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
PHYSICIAN_CERTIFICATION,
VE_SIGNATURE,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME
 [CO]
RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
COMMENT_DATE,
DESCRIPTION,
STATUS_CODE,
STATUS_DATE
 [EN]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
ENTITY_TYPE,
LICENSE_ID,
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
SGIN,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
STATUS_CODE,
LICENSE_TYPE_37,
LINKED_ID,
LINKED_CALLSIGN,
 
  [HD]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
RESERVED_1,
ALIEN,
ALIEN_GOVERNMENT,
ALIEN_CORPORATION,
ALIEN_OFFICER,
ALIEN_CONTROL,
REVOKED,
CONVICTED,
ADJUDGED,
RESERVED_2,
COMMON_CARRIER,
NON_COMMON_CARRIER,
PRIVATE_COMM,
FIXED,
MOBILE,
RADIOLOCATION,
SATELLITE,
DEVELOPMENTAL_STA_DEMONSTRATION,
INTERCONNECTED_SERVICE,
CERTIFIER_FIRST_NAME,
CERTIFIER_MIDDLE_INITIAL,
CERTIFIER_LAST_NAME,
CERTIFIER_SUFFIX,
CERTIFIER_TITLE,
FEMALE,
BLACK_AFRICAN_AMERICAN,
NATIVE_AMERICAN,
HAWAIIAN,
ASIAN,
WHITE,
HISPANIC,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
AUCTION_ID,
BROADCAST_SERVICES_REGULATORY_STATUS,
BAND_MANAGER_REGULATORY_STATUS,
BROADCAST_SERVICES_SERVICE_TYPE,
ALIEN_RULING,
LICENSEE_NAME_CHANGE,
WHITESPACE_INDICATOR
 [HS]
RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
LOG_DATE,
CODE
 [LA]
RECORD_TYPE,
ID,
CALLSIGN,
ATTACHMENT_CODE,
ATTACHMENT_DESCRIPTION,
ATTACHMENT_DATE,
ATTACHMENT_FILENAME,
ACTION_PERFORMED
 [SC]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
SPECIAL_CONDITION_TYPE,
SPECIAL_CONDITION_CODE,
STATUS_CODE,
STATUS_DATE
 [SF]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_FREEFORM_TYPE,
UNIQUE_LICENSE_FREEFORM_ID,
SEQUENCE_NUMBER,
LICENSE_FREEFORM_CONDITION,
STATUS_CODE,
STATUS_DATE

The 50 output fields selected from the above lists are (arranged in groups of ten for easy counting):

ID,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,

PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME,
COMMENT_DATE,
DESCRIPTION,
CO_STATUS_CODE, (i.e., STATUS_CODE from [CO])
CO_STATUS_DATE, (i.e., STATUS_DATE from [CO])
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,

LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,

ATTENTION_LINE,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
EN_STATUS_CODE, (i.e., STATUS_CODE from [EN])
EN_STATUS_DATE, (i.e., STATUS_DATE from [EN])
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,

CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
REVOKED,
CONVICTED,
ADJUDGED,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
LICENSEE_NAME_CHANGE,
LINKED_ID,
LINKED_CALLSIGN
The contents of these fields are based on the original equivalent entries in the original data files. The entries for the fields are subject to the following transformations before being written to the output file:
  • The entry is converted to upper case;
  • Any line feeds (yes, the FCC allows line feeds within a field) are converted to the four-character sequence: <LF>;
  • Leading and trailing spaces are removed;
  • If the field is a date, it is converted from FCC format (mm/dd/yyyy) to ISO 8601 extended format: YYYY-MM-DD.
The latest output file created in this manner (and its MD5 checksum) may be downloaded from this directory.

The full source code to generate the output file may be downloaded here.

To create the binary from the source code, go to the directory that contains the makefile and type:
make fcc-db
This should generate the executable program as: bin/fcc-db. The program may be executed from within the bin directory as:
fcc-db [directory]
where [directory] is the name of the directory that contains the input FCC AM.dat, CO.dat, EN.dat and HD.dat files. Those files should be processed and the output written to stdout.

For what it's worth, it takes somewhat less than 15 seconds for the program to execute to completion on my desktop computer if stdout is redirected to an output file.


2020-10-12

Creating Local FCC Database From Version 4 Data

This is a followup to this post, required because the FCC has changed the format of their files from version 3 to version 4.

The contents of the eight FCC files are now (as described in the version 4 document):

Amateur
[AM] -- unchanged from version 3
1   Record Type [AM]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Operator Class              char(1)
7   Group Code                  char(1)
8   Region Code                 tinyint
9   Trustee Call Sign           char(10)
10  Trustee Indicator           char(1)
11  Physician Certification     char(1)
12  VE Signature                char(1)
13  Systematic Call Sign Change char(1)
14  Vanity Call Sign Change     char(1)
15  Vanity Relationship         char(12)
16  Previous Call Sign          char(10)
17  Previous Operator Class     char(1)
18  Trustee Name                varchar(50)

Comments
[CO] -- unchanged from version 3
1   Record Type [CO]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Comment Date                mm/dd/yyyy
6   Description                 varchar(255)
7   Status Code                 char(1)
8   Status Date                 mm/dd/yyyy

Entity
[EN] -- unchanged from version 3
1   Record Type [EN]                char(2)
2   Unique System Identifier        numeric(9,0)
3   ULS File Number                 char(14)
4   EBF Number                      varchar(30)
5   Call Sign                       char(10)
6   Entity Type                     char(2)
7   Licensee ID                     char(9)
8   Entity Name                     varchar(200)
9   First Name                      varchar(20)
10  MI                              char(1)
11  Last Name                       varchar(20)
12  Suffix                          char(3)
13  Phone                           char(10)
14  Fax                             char(10)
15  Email                           varchar(50)
16  Street Address                  varchar(60)
17  City                            varchar(20)
18  State                           char(2)
19  Zip Code                        char(9)
20  PO Box                          varchar(20)
21  Attention Line                  varchar(35)
22  SGIN                            char(3)
23  FCC Registration Number (FRN)   char(10)
24  Applicant Type Code             char(1)
25  Applicant Type Code Other       char(40)
26  Status Code                     char(1)
27  Status Date                     mm/dd/yyyy
 
Application/License Header
[HD]
1   Record Type [HD]                            char(2)
2   Unique System Identifier                    numeric(9,0)
3   ULS File Number                             char(14)
4   EBF Number                                  varchar(30)
5   Call Sign                                   char(10)
6   License Status                              char(1)
7   Radio Service Code                          char(2)
8   Grant Date                                  mm/dd/yyyy
9   Expired Date                                mm/dd/yyyy
10  Cancellation Date                           mm/dd/yyyy
11  Eligibility Rule Num                        char(10)
12  Reserved                                    char(1)
13  Alien                                       char(1)
14  Alien Government                            char(1)
15  Alien Corporation                           char(1)
16  Alien Officer                               char(1)
17  Alien Control                               char(1)
18  Revoked                                     char(1)
19  Convicted                                   char(1)
20  Adjudged                                    char(1)
21  Reserved                                    char(1)
22  Common Carrier                              char(1)
23  Non Common Carrier                          char(1)
24  Private Comm                                char(1)
25  Fixed                                       char(1)
26  Mobile                                      char(1)
27  Radiolocation                               char(1)
28  Satellite                                   char(1)
29  Developmental or STA or Demonstration       char(1)
30  InterconnectedService                       char(1)
31  Certifier First Name                        varchar(20)
32  Certifier MI                                char(1)
33  Certifier Last Name                         varchar(20)
34  Certifier Suffix                            char(3)
35  Certifier Title                             char(40)
36  Female                                      char(1)
37  Black or African-American                   char(1)
38  Native American                             char(1)
39  Hawaiian                                    char(1)
40  Asian                                       char(1)
41  White                                       char(1)
42  Hispanic                                    char(1)
43  Effective Date                              mm/dd/yyyy
44  Last Action Date                            mm/dd/yyyy
45  Auction ID                                  integer
46  Broadcast Services - Regulatory Status      char(1)
47  Band Manager - Regulatory Status            char(1)
48  Broadcast Services - Type of Radio Service  char(1)
49  Alien Ruling                                char(1)
50  Licensee Name Change                        char(1)
51  Whitespace Indicator                        char(1)
52  Operation/Performance Requirement Choice    char(1)
53  Operation/Performance Requirement Answer    char(1)
54  Discontinuation of Service                  char(1)
55  Regulatory Compliance                       char(1)

History
[HS] -- unchanged from version 3
1   Record Type [HS]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   Call Sign                   char(10)
5   Log Date                    mm/dd/yyyy
6   Code                        char(6)

License Attachment
[LA] -- unchanged from version 3
1   Record Type [LA]            char(2)
2   Unique System Identifier    numeric(9,0)
3   Call Sign                   char(10)
4   Attachment Code             char(1)
5   Attachment Description      varchar(60)
6   Attachment Date             mm/dd/yyyy
7   Attachment File Name        varchar(60)
8   Action Performed            char(1)

Special Condition
[SC] -- unchanged from version 3
1   Record Type [SC]            char(2)
2   Unique System Identifier    numeric(9,0)
3   ULS File Number             char(14)
4   EBF Number                  varchar(30)
5   Call Sign                   char(10)
6   Special Condition Type      char(1)
7   Special Condition Code      int
8   Status Code                 char(1)
9   Status Date                 mm/dd/yyyy

License Free Form Special Condition
Position Data Element Definition
[SF] -- unchanged from version 3
1   Record Type [SF]                    char(2)
2   Unique System Identifier            numeric(9,0)
3   ULS File Number                     char(14)
4   EBF Number                          varchar(30)
5   Call Sign                           char(10)
6   License Free Form Type              char(1)
7   Unique License Free Form Identifier numeric(9,0)
8   Sequence Number                     integer
9   License Free Form Condition         varchar(255)
10  Status Code                         char(1)
11  Status Date                         mm/dd/yyyy
The following extract from the code that creates the output database maps these on a one-to-one basis to internal identifiers (the four new fields in the [HD] records don't seem to be important -- at least for now -- so there is no change in the output as compared to the processing of version 3 files, although the new fields are processed):

[AM]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
PHYSICIAN_CERTIFICATION,
VE_SIGNATURE,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME
 [CO]
RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
COMMENT_DATE,
DESCRIPTION,
STATUS_CODE,
STATUS_DATE
 [EN]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
ENTITY_TYPE,
LICENSE_ID,
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
SGIN,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
STATUS_CODE,
STATUS_DATE
 [HD]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
RESERVED_1,
ALIEN,
ALIEN_GOVERNMENT,
ALIEN_CORPORATION,
ALIEN_OFFICER,
ALIEN_CONTROL,
REVOKED,
CONVICTED,
ADJUDGED,
RESERVED_2,
COMMON_CARRIER,
NON_COMMON_CARRIER,
PRIVATE_COMM,
FIXED,
MOBILE,
RADIOLOCATION,
SATELLITE,
DEVELOPMENTAL_STA_DEMONSTRATION,
INTERCONNECTED_SERVICE,
CERTIFIER_FIRST_NAME,
CERTIFIER_MIDDLE_INITIAL,
CERTIFIER_LAST_NAME,
CERTIFIER_SUFFIX,
CERTIFIER_TITLE,
FEMALE,
BLACK_AFRICAN_AMERICAN,
NATIVE_AMERICAN,
HAWAIIAN,
ASIAN,
WHITE,
HISPANIC,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
AUCTION_ID,
BROADCAST_SERVICES_REGULATORY_STATUS,
BAND_MANAGER_REGULATORY_STATUS,
BROADCAST_SERVICES_SERVICE_TYPE,
ALIEN_RULING,
LICENSEE_NAME_CHANGE,
WHITESPACE_INDICATOR
 [HS]
RECORD_TYPE,
ID,
ULS_NUMBER,
CALLSIGN,
LOG_DATE,
CODE
 [LA]
RECORD_TYPE,
ID,
CALLSIGN,
ATTACHMENT_CODE,
ATTACHMENT_DESCRIPTION,
ATTACHMENT_DATE,
ATTACHMENT_FILENAME,
ACTION_PERFORMED
 [SC]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
SPECIAL_CONDITION_TYPE,
SPECIAL_CONDITION_CODE,
STATUS_CODE,
STATUS_DATE
 [SF]
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_FREEFORM_TYPE,
UNIQUE_LICENSE_FREEFORM_ID,
SEQUENCE_NUMBER,
LICENSE_FREEFORM_CONDITION,
STATUS_CODE,
STATUS_DATE

The 48 output fields selected from the above lists are (arranged in groups of ten for easy counting):

ID,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,

PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME,
COMMENT_DATE,
DESCRIPTION,
CO_STATUS_CODE, (i.e., STATUS_CODE from [CO])
CO_STATUS_DATE, (i.e., STATUS_DATE from [CO])
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,

LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,

ATTENTION_LINE,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
EN_STATUS_CODE, (i.e., STATUS_CODE from [EN])
EN_STATUS_DATE, (i.e., STATUS_DATE from [EN])
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,

CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
REVOKED,
CONVICTED,
ADJUDGED,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
LICENSEE_NAME_CHANGE
The contents of these fields are based on the original equivalent entries in the original data files. The entries for the fields are subject to the following transformations before being written to the output file:
  • The entry is converted to upper case;
  • Any line feeds (yes, the FCC allows line feeds within a field) are converted to the four-character sequence: <LF>;
  • Leading and trailing spaces are removed;
  • If the field is a date, it is converted from FCC format (mm/dd/yyyy) to ISO 8601 extended format: YYYY-MM-DD.
The latest output file created in this manner (and its MD5 checksum) may be downloaded from this directory.

The full source code to generate the output file may be downloaded here.

To create the binary from the source code, go to the directory that contains the makefile and type:
make fcc-db
This should generate the executable program as: bin/fcc-db. The program may be executed from within the bin directory as:
fcc-db [directory]
where [directory] is the name of the directory that contains the input FCC AM.dat, CO.dat, EN.dat and HD.dat files. Those files should be processed and the output written to stdout.

For what it's worth, it takes somewhat less than 15 seconds for the program to execute to completion on my desktop computer if stdout is redirected to an output file.